In the query for the second combo box, add all the address fields. Then in the After_Update event for that box, put code to fill the address fields from those fields:
Me.txtAddress1 = Me.cboSecondCombo.Column(2) Me.txtAddress2 = Me.cboSecondCombo.Column(3) and so on. Combo box column numbering starts with 0, so that's usually the key field, 1 is the field that shows, and the others need not be visible (set column widths to 0). Be sure to reset the number of columns also. Tobi -----Original Message----- From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of jezmo_codpiece Sent: Wednesday, July 13, 2005 12:33 PM To: AccessDevelopers@yahoogroups.com Subject: [AccessDevelopers] Re: Can you check this code please Hi Tobi, the form is based on a Quote table, and I am using it to generate new quotes. The user selects the customer from a combo box, this then limits the avilable addresses in a second combo box, which pokes the AddressID in to the txtAddressID text box mentioned in the code. I have tried pulling all the fields from the second combo box but it doesn't work reliably. I always get the addressID field, but the rest of the address fields are hit and miss (mostly miss). Hence the current effort. If you know of a better way please let me know. Thanks. --- In AccessDevelopers@yahoogroups.com, "Hoffman, Tobi K \(DYS\)" <[EMAIL PROTECTED]> wrote: > What is the form based on? Seems to me it would be far easier to create a query that includes the address fields to base the form on, and then there's no complicated look-up code needed at all. > > Tobi > > -----Original Message----- > From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of jezmo_codpiece > Sent: Wednesday, July 13, 2005 12:19 PM > To: AccessDevelopers@yahoogroups.com > Subject: [AccessDevelopers] Can you check this code please > > > I am using the value from a textbox (txtDeliveryAddressID) on the > form (frmQuote) as a criteria for the query > qryDeliveryAddressForQuote, so that the query returns the relevant > address for the record selected on the form. This works fine, if I > run the query manually I get the correct address. What I want to do > then is poke the rest of the address fields in to the fields on the > form, but I cannot get this to work. Essentially I have said that > when txtDeliveryAddressID changes, run the following code. I have > only put the city field in so far to test it, but it does t work and > I cannot see why. Can anybody help please? > > Private Sub txtDeliveryAddressID_Change() > On Error GoTo Err_txtDeliveryAddressID_Change > > Dim MyDB As Database > Dim Myrec As DAO.Recordset > > Dim MyQuery As String > > Set MyDB = CurrentDb > Set MyQuery = "qryDeliveryAddressForQuote" > Set Myrec = MyDB.OpenRecordset(MyQuery, dbOpenSnapshot) > > > Myrec.MoveFirst > > Set Forms!frmQuote!txtDeliveryCity = Myrec!DeliveryCity > > > Myrec.Close > MyDB.Close > > > Exit_txtDeliveryAddressID_Change: > Exit Sub > > Err_txtDeliveryAddressID_Change: > MsgBox Err.Description > Resume Exit_txtDeliveryAddressID_Change > > End Sub Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
