Perhaps it would help if instead of saying "it doesn't work" or "still no joy", tell us what specifically is going wrong. Is the code failing at some point? Is the field just not being filled properly? What is the current code you are using? If you are still using a hard-coded query, post the SQL of that query.
HTH, Toby ----- Original Message ----- From: "jezmo_codpiece" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Wednesday, July 13, 2005 9:37 AM Subject: [AccessDevelopers] Re: Can you check this code please > Hi Tom, have tried it as you have suggested, but still no joy! I am > rapidly losing what hair I have at the moment. Thanks for your > suggestion. > > > > --- In [email protected], "Tom Oakes" <[EMAIL PROTECTED]> > wrote: > > I would remove the parameter from the query and do it in code. > Placing > > hard-coded parameters in your queries greatly limits their use. > > > > > > Private Sub txtDeliveryAddressID_Change() > > On Error GoTo Err_txtDeliveryAddressID_Change > > > > Dim MyDB As DAO.Database > > Dim Myrec As DAO.Recordset > > Dim MyQuery As String > > Dim strSQL As String > > > > strSQL = "SELECT * FROM qryDeliveryAddressForQuote WHERE > > DeliveryAddressID=" & Me.txtDeliveryAddressID > > > > Set MyDB = CurrentDb > > Set MyQuery = "qryDeliveryAddressForQuote" > > Set Myrec = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly) '<-- > > forward-only should be faster > > > > 'Myrec.MoveFirst '<-- don't need this > > > > If Not Myrec.EOF then > > Forms!frmQuote!txtDeliveryCity = Myrec!DeliveryCity > > Else > > Msgbox "Record not found." > > End If > > > > Myrec.Close > > MyDB.Close > > > > Exit_txtDeliveryAddressID_Change: > > Exit Sub > > Err_txtDeliveryAddressID_Change: > > MsgBox Err.Description > > Resume Exit_txtDeliveryAddressID_Change > > End Sub > > > > > > Tom Oakes > > Personal PC Consultants, Inc. > > [EMAIL PROTECTED] > > 503.230.0911 (O) > > 402.968.6946 (C) > > 713.583.7091 (F) > > > > > > > > > > _____ > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of > jezmo_codpiece > > Sent: Wednesday, July 13, 2005 9:19 AM > > To: [email protected] > > 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 > > > > > > > > * Visit your group "AccessDevelopers > > <http://groups.yahoo.com/group/AccessDevelopers> " on the web. > > > > > > * To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED] > subject=Unsubscribe> > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service > > <http://docs.yahoo.com/info/terms/> . > > > > > > _____ > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > > > > > > 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/
