PK in combo box automatically populates two other text boxes

[Calvin]

your velcome

 

[maya]

what happen?! i just try selecting from the combo and the textbox is not changing now...why? i did as how u told but suddenly its not workingseek ur help again...

 

[Calvin]

Try placing a breakpoint at the begining of the State_AfterUpate event and use the "step into" button on the toolbar to step through the code one line at time and use your cursor to hover over the varible name and controls to see what values they are storing or you can add a Debug.Print statment to code or add a Watch to see what the variables and controls are storing.

You have 16 columns in your combo box are you sure that every record has a value in each field/column?

 

[Calvin]

otherwise I think I'm going to have to ask that you upload a sample.mdb file with the form in it so I can see everything involved.

 

[maya]

its ok...ive solved it becoz i copied and paste the same code in all the forms that needed it as the name was the same...but i realise that i cant do that and thats why its not working so i have to do for 16 textboxes in each form...a hassle i would say

 

[Calvin]

Why not copy the combo box to each form or even the entire form and just rename the parts that need to be renamed.

have fun

 

[maya]

its ok

anyway ive done it so its ok...but ill definitely use ur idea next time....thanx a lot for ur help and sorry if i sounded too asking for help...hehehehe.....take care and have fun....

 

[pcEars]

Calvin,

Thanks for the tasty bit of coding with the City, State,and Zip!

I added it to my form, and it's almost perfect.

Is it possible to add a line or two that will force a new record in the zip table if the user enters a new city or state directly in the textbox?

 

[Calvin]

pcEars,

Sure, you should be able to figure something out, make sure to set the combo property Limit to List = No and then look into using the Not In List event to insert the new data into the table that is used in the rowsource of the combo box.
Using an Insert SQL statement should be the easiest for getting the new data in the table. If you need to collect more than one field of information, consider using an InputBox or a custom popup form to gather the rest of the required info for the record before you attempt the Insert.
Don't forget to refresh/requery the rowsource of the combo box after the data has been added to the table, you may need to retrigger the Combo_AfterUpdate event also to insure that the other textboxes still get updated.

That is a very good question , I havn't messed with a Not In List event in a while. I usually control data that is in a combobox by other means. Have fun with it, make the tools work for you.

 

[Calvin]

If you use two textboxes for and already know what the City and State are and then you attempt to add a zipcode in a combo box that is not listed for that city or state you could use something like the following for the update:

Private Sub cboZip_NotInList(NewData As String, Response As Integer)
on error resume next
if me.txtCity="" or me.txtState="" then exit sub
dim strSQL as string
strSQL = "INSERT INTO MyTable ([City], [State], [Zip]) VALUES ('" & me.txtCity & "', '" & me.txtState & "', '" & NewData & "')"

CurrentDB.Execute strsql

if err.number =0 then 
    msgbox "Record Inserted"
else
    msgbox "There was a error inserting the record" & vbcr & vbcr & "Error: " & err.number & vbcr & "Desc: " & err.description
end if
End Sub

This is off the top of my head and isn't bullet proof but hopefully this will be enough to get you started. I'll be around if you have more questions.

 

[pcEars]

I'll give it a try, and thanks for your help.

 

[maya]

new problem

calvin, ive just encountered a new problem....

not all the fields in my table have records...and now the new problem is that when i choose the country and the state, the information displayed is not in the textbox where it should be...i dunno why the textbox is not refering to the columns in the state combo..

for example population data is displayed in the weather textbox and so on....do you know why is this happening....thanx a lot...

 

[pcEars]

Calvin,

Sorry I neglected to get back to you
(I've got some lame excuse about lack of time)
but I did want to let you know that I was able to use the bit of code you offered, and I did get the On Not In List event to fire it.

Again, Many Thanks!

 

[King_kamikaze]

Using this technique, is there any way i can get the combobox to feed into a table?

If i set the control source to the field i need can i specify which column from the combobox it inputs?

I.e MyField = me.cboState.Column(2)

Many thanks
Tim