Autofill from Query

[sparkbyte]

I have a Main Form with 3 SubForms.

What I am trying to do is to convert a lookup combobox to use a query.

This is back ended to MS SQL 2k currently.

MainActivity
AddNewCace UpdateCase AddUpdateCustContacts

On AddnewCase and UpdateCase there is a lookup from the TblContacts that consists of a ComboBox drop down that the data source is a query (QryContactsLookup) That is sorted by ContactName and only shows the the ContactName in the drop down.

Private Sub ContactAll_Click()
'    Me[ContactName] = QryContactsLookup.Fields("tblContactName")
    Me![ContactPhone] = QryContactsLookup.Fields("tblContactPhone")
    Me![ContactFax] = QryContactsLookup.Fields("tblContactFax")
    Me![OfficeCode] = QryContactsLookup.Fields("tblOfficeCode")
    Me![ProgramName] = QryContactsLookup.Fields("tblProgram")
End Sub

This is what it is now.

Private Sub ContactAll_Click()
    Me![ContactName] = [ContactAll].[Column](1)
    Me![ContactPhone] = [ContactAll].[Column](2)
    Me![ContactFax] = [ContactAll].[Column](3)
    Me![OfficeCode] = [ContactAll].[Column](4)
    Me![ProgramName] = [ContactAll].[Column](5)
End Sub

Can I autofill the 4 fields using the query and combo box without the column referrences?




Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[sparkbyte]

On other question. When I have the fields autofill through VBA the Phone, Fax, Program Name, and Calling office are getting filled in with the first records data when the form first loads. I need these to be empty until the combobox is used to choose the calling party.

Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[dhookom]

Why do you think you need to use code to store these values in multiple tables? One of the advantages of a relational database is that you can store a value in one table and make it available in queries etc.

Against my better judgement, you could replace the query with something like:

Me![OfficeCode] = DLookup("some field", "some table or query","some where condition")

Duane
Hook'D on Access
MS Access MVP