Assistance with Dlookup on Form

[kentwood]

I know to most this is simple, but as a newer user putting everything in its right place is hard. Have checked multiple posts for my situation and cannot locate. I know I need to use DLookup.

Membership table used to built form has among others, a contract #1 field (9-digit Soc.Sec. #) and a Contract #2 field with another randomly assigned 9 digit number. Current form set up to accept contract#1 in an unbound text box which then autopopulates other fields pertnent to this member. Member should be giving us Contract #2 but that will not always happen. Want a DLookup so that if the #1 num is put in it will automatically input the correct #2 number.

Not sure where to put it and exactly how to format. Can someone please help me. Both fields on the same table.

 

[RuralGuy]

Why are you using unbound controls?

 

[kentwood]

I am using an after update event as below:

Private Sub ContractNum_AfterUpdate()
Dim szSQL As String

szSQL = "select * from qryMembership where Contract_Num = """ & Trim(Me.ContractNum) & """;"

Me.RecordSource = szSQL
End Sub

This is what populates the additional membership fields on my form giving specific information for the contract input.

 

[RuralGuy]

I would think you would bind your form to qryMembership and have ContractNum a CBO that looks up a record in your query for you.

 

[kentwood]

If you mean use a Combo box, I went that route initially. However, there are about 900 records now and more coming so we felt that it was not the most streamline direction to date and the users did not like the idea going through such a long list.

That is the reason for leaning toward the DLookup. Formatting is the biggest issue.

 

[RuralGuy]

Maybe this link will offer some ideas: Combos with Tens of Thousands of Records

 

[kentwood]

Thanks. Very interesting for a novice like myself, so I will give it a try.