Populating a field by using the value of another field

[humtake]

Hello all!

I have been Googling for a week now trying to figure out why my code won't work in this form. It seems like a simple task (don't judge by the length of this post, it really does seem like it should be simple). I'm not a savvy database guy but can work through any logic, it's usually the semantics that I get caught up on and I think that's the case now...

Details of database:

• Two tables exist (SOX and PAYMENT).
  
  • Although the PK's from each table use different unique identifiers, there can be records from each that correspond to the other. For instance, a PAYMENT record of 2016-01 may be the same thing as SOX record 1.2016.01.
  
  
  
  
• 1 form that I use to search for a PAYMENT number and it populates a number of text boxes based on fields from the PAYMENT table.
  
  • In the form, there is a text box that will have the SOX number (txt_PaymentSupportingReferenceID) that matches the PAYMENT record (if there is one)

What I'm trying to do:
If I look up a record and it has a SOX number, I want to click a button to populate other text boxes. For instance, another field in the form is txt_PrestoRefControlReferenceID which would be blank until I hit a View Details button, which would then populate that field from the SOX table based on the number in the txt_PaymentSupportingReferenceID text box. Hope that makes sense.

Problem with my code:
My code is pasted below. When I hit the button, all it does is paste the text in the Task line (i.e. the query itself is pasted when I click the button). I understand the logic of why it does that, but I'm trying to get it to put the RESULT of the SQL statement in the txt_PaymentRefControlReferenceID textbox. What am I missing?

Private Sub cmd_PaymentOpenSupportingID_Click()
Dim SOXRecord As String
Dim Task As String
    
    SOXRecord = txt_PaymentSupportingReferenceID.Value
    Task = "SELECT [Control Reference ID] FROM tbl_SOXDeficiencies WHERE ((Reference_ID Like ""*" & SOXRecord & "*""))"
    Me.txt_PaymentRefControlReferenceID = Task
End Sub

An alternative I was going to do based on my Google results was just have a button that opens a new form that would populate all of the fields from the SOX table. I found the code below but I get some weird error that says no specific message. I'd rather do the above method but can do this if it makes it easier to get this done; I'm sick of working on this little problem.

DoCmd.OpenForm "frm_Add_Update_SOX_Deficiencies", , , "Reference_ID=" & Me!txt_PaymentSupportingReferenceID.Value

Thanks all, I appreciate any help you can give me!

[aytee111]

To get the result, use the DLookup function.

Another way is to filter the form - your suggestion to open another form with the reference ID as the filter, you can use this form and filter it (if that is what you are trying to do).
Me.Filter="Reference_ID=" & Me!txt_PaymentSupportingReferenceID
Me.FilterOn=True
Me.Requery

[humtake]

Thank you. I tried the code you offered but I get the same error I have been getting whenever there isn't an obvious error, which is:

Run-time error '3000':

Reserved error (-3201); there is no message for this error.

Also, I assume I paste your code under the DoCmd and erase the criteria from it?

    ' DoCmd.OpenForm "frm_Add_Update_SOX_Deficiencies" ' , , , "Reference_ID=" & Me!txt_PaymentSupportingReferenceID.Value
    
    Me.Filter = "Reference_ID=" & Me!txt_PaymentSupportingReferenceID
    Me.FilterOn = True
    Me.Requery

[aytee111]

Do you have a Record Source for your form?

Is the Reference_ID a valid field in the Record Source and on your form?

Is there a value in txt_PaymentSupportingReferenceID when this code runs? And is it a numeric field?

This field must have an empty Control Source.

[humtake]

The form is bound to the PAYMENT table but no Control Source.

The Reference_ID is the column in the SOX table that I'm trying to match (If frm_Add/Update Payment Exceptions.txt_PaymentSupportingReferenceID = SOX.Reference_ID, then I want it to populate the information from that Reference_ID). It is not in the form.

Yes, there is a value in txt_PaymentSupportingReferenceID, see item directly above. I'm trying to take the value that is already in this field, find the matching record in the SOX table (Reference_ID), and then populate the rest of the text boxes in the form with the corresponding info from that record.

The txt_PaymentSupportingReferenceID does not have an empty control source since it is bound to the PAYMENTS table.

[humtake]

Oh, also, txt_PaymentSupportingReferenceID is set as Text. This is because the ID can be many different alphanumeric values.

[humtake]

Sorry, that first line may seem confusing. Yes, there is a Record Source because it is bound to the PAYMENT table. Ignore what I said about the Control Source :-)

[aytee111]

I would suggest that you make a subform based on the SOX table. Either that or populate each field by doing a DLookup for each one - rather cumbersome and slow. You would link the subform to the main form using the Reference_ID, you wouldn't have to click a button to get the SOX information to show, it would be done thru the form links.

[aytee111]

The way you have the field referenced above it is a numeric field. In order to reference a text field you must surround it with quotes:

"Reference_ID='" & Me!txt_PaymentSupportingReferenceID & "'"

That's for future reference!

[humtake]

Thanks so much! I got the subform to work but it has a major limitation, which is that right now it only works if there is only one ID in the txt_PrestoSupportingReferenceID field. In some cases, there could be more than one.

I didn't realize a subform could help me like this but once you mentioned the linking I tried it and was able to get it to work. Ideally, I want it this way where it shows the record automatically if one exists. However, I'm thinking it's going to get very muddled if I want it to be more than one. But that's a project for tomorrow, my brain hurts already today. Thanks again :-)

[aytee111]

The subform can be a continuous form and show as many records as you want.

Sorry to hear about your sore brain!