Criteria From "Text Box" not pushing to new form

[steve21nj]

Hello,

I am having issues passing a text box field's criteria into another bound field in another form.

My main form is called AddPurchaseOrder. I have a text box in that form called RequisitionNumber. In my form I have a command button that opens another form "DHS1501," both forms will be open.

My query criteria calls on the RequisitionNumber:
[Forms]![AddPurchaseOrder]![RequisitionNumber]

The code on the command to open the DHS1501 form is:

Private Sub Command350_Click()
On Error GoTo Err_Command350_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "DHS1501"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command350_Click:
Exit Sub
Err_Command350_Click:
MsgBox Err.Description
Resume Exit_Command350_Click

End Sub

It never returns a value. What am I missing?
Thank you,

 

[Beetle]

My query criteria calls on the RequisitionNumber:
[Forms]![AddPurchaseOrder]![RequisitionNumber]

What query are we talking about here? Is this a query that is used as the Record Source of the DHS1501 form? If so, does that query return the RequisitionNumber column from that table and your form reference is in the Criteria row for that column? More details please.

Also, your code is a bit incomplete (looks like Wizard created code) because you attempt to use the stLinkCriteria variable in the Where condition of the OpenForm method, but no value was ever assigned to that variable;

Private Sub Command350_Click()
On Error GoTo Err_Command350_Click

Dim stDocName As String     [COLOR="Red"]   <<< Variables are declared here[/COLOR]
Dim stLinkCriteria As String   [COLOR="red"]<<< "                         "[/COLOR]

stDocName = "DHS1501"  [COLOR="red"]<<< This variable is assigned the form name as its value[/COLOR]

DoCmd.OpenForm stDocName, , , stLinkCriteria [COLOR="red"]<<< No value ever assigned to this variable[/COLOR]

Exit_Command350_Click:
Exit Sub

Err_Command350_Click:
MsgBox Err.Description
Resume Exit_Command350_Click

 

[boblarson]

And just some extra info. The form will not respond to the DoCmd.OpenForm if it is already opened. If you want to filter it while open, you can either requery the form via code or use code to use the Filter.

 

[DSquared65]

If the idea is just to display the data from form1 into a field in form2 then just reference the field from form 1 into a text box in form2 In the text box in form2 type =[Forms]![AddPurchaseOrder]![RequisitionNumber]
Whenever the form opens it will display the contents of the field from form1 without requerying or refreshing.

 

[steve21nj]

Thank you for the responses. I decided to move away from the query, considering I left that and everything else out in the beginning. So as of now, the feed works.

One problem I have as you will see below comes from my fk_"" values. It pulls the data into the form, but the auto number value, not the text value. Is there a fix for that?

For example.... fk_VendorID is 4, but the Column in the table I need is "Vendor" Jim.

Private Sub btnDHS_Click()
On Error GoTo Err_btnDHS_Click
    Dim strDocName As String
 
    strDocName = "DHS1501"
    DoCmd.OpenForm strDocName
    With Forms(strDocName)
    .Caption = "Caption set from AddPurchaseOrder"
    .txtDocNum = Me.RequisitionNumber
    .txtCardHolder = Me.fk_CardHolderID
    .txtVendor = Me.fk_VendorID
    .txtJustPurchase = Me.Description
    End With
Exit_btnDHS_Click:
    Exit Sub
Err_btnDHS_Click:
Select Case Err.Number
    Case 94
       'Code Here
    Case Else
        MsgBox Err.Description & " Number " & Err.Number
        Resume Exit_btnDHS_Click
End Select
End Sub

Thank you again.

 

[boblarson]

One problem I have as you will see below comes from my fk_"" values. It pulls the data into the form, but the auto number value, not the text value. Is there a fix for that?

Your explanation there makes me believe that you have used a lookup directly at table level when you should NOT do that. See here why lookups directly at table level for fields should be avoided:
http://www.mvps.org/access/lookupfields.htm

You can use them at FORM level, but not at table level.

So, follow this:
http://www.btabdevelopment.com/ts/removelookups

And then, in your query you will add the table in which has the text info as well and use that field from it after linking the applicable ID field from that table to your form's table.

 

[steve21nj]

Boblarson,

Thank you for the read. I went through both links, more importantly the second. I validated that I do not have the lookups on, they were already set to text.

I am still learning the “best” practices of queries. I set up a basic query “qryPurchaseOrderLog.” The data pulled from this record set with the corresponding linked tables (as suggested), a big thank you to that!

Now my issue comes from the data when there is more than one line item on a purchase.

For ex. Item Description, Stock Number, Quantity, etc. The data will populate the lines, but it will only take the data from the first set of data and not move to the second line item under the same transaction. I provided a zip word doc of the screen shots to show what I mean. I have not bound the lines 3-10, just the first 2 lines to get me started.

Any thoughts?