Cascading combos that autofill

[lmnop7854]

I am about to tear my hair out. I am writing this question, and then leaving work, because I can't take this anymore.

I have a questionnaire-type form, and it is based on one table. It is structured like a questionnaire, but the data it is trakcing is contract parameters, so each question is a different parameter. I have 3 fields that will determine a specific contract - Provider Name, Contract Type, and Eff Date. I have 3 cascading combo boxes that are working fine, based on the sole table. What I would like to do is develop an update form so that I can pick from the 3 combos, and the data about that contract will autofill the form.

The update form is also based on the table, but not all of the fields will autofill with the correct data when I pick from the combos. Can anyone help me? I have been working on this for weeks, and I already have SJMcAbney's thread on cascading combos that I have been using, and Pat Hartman's sample database on autopopulating fields. I can't get anything to work, and my boss is no help either.

I am hoping that when I come back to work in the morning, enlightenment will be here. Thank you all for the help you have given me on this so far.

 

[Liv Manto]

First Create a query that would capture 3 sample data.

Select * from Table1 where Provider Name = "Kay" and Contract Type = 'T" and Eff Date = '11/23/2004'




Assumptions - your formname is formupdate and the text in your forms has all the fields from the table.


'On After Update of the combo box, capture the data into three variables. V1, V2, V3 , how to capture data ---> v1= me.combo1:v2 = combo2 etc. If you want to check if its capturing
correct data, put the variables in to a msgbox ---> Msgbox V1 & " " & V2 & " " & V3


'Create an String variable for your SQL.
dim strsql as string


'change the data - KAY, T, 11/23/2004 with the three variables


strsql = "Select * from Table1 where Provider Name = ' " & V1 & "' and Contract Type = '" & V2 & "' and Eff Date = '" & V3 & "'"


'Note that the double quote in "KAY" should be replaced by a single quote in the above syntax.

"if you want to check if your SQL syntax is correct, put it in msgbox ---> msgbox strsql

me.formupdate.recordsource = strsql
me.formupdate.refresh