RE: [AccessDevelopers] Re: Can you check this code please

[Tom Oakes]

Sorry - I forgot to mention that I uploaded a revised 
front-end.  It's in the "Help Needed With" folder.
 

Tom Oakes Personal PC Consultants, Inc. [EMAIL PROTECTED] 503.230.0911 (O) 402.968.6946 (C) 713.583.7091 (F) 


From: AccessDevelopers@yahoogroups.com 
[mailto:[EMAIL PROTECTED] On Behalf Of Tom 
OakesSent: Wednesday, July 13, 2005 1:37 PMTo: 
AccessDevelopers@yahoogroups.comSubject: RE: [AccessDevelopers] Re: 
Can you check this code please

Two things, both syntactical: 
 
1) Get rid of this line: 
Set MyQuery = 
"qryDeliveryAddressForQuote"
 
That variable is not declared, the "Set" keyword is 
only required for object variables, and we're no longer using it.  This was 
throwing an error on my machine.  
 
2) You hadn't changed the recordset's source: 

Set Myrec = 
MyDB.OpenRecordset(strSQL,dbOpenForwardOnly)
 
However, Tobi 
Hoffman's recommendation about using the combo is a much better (and faster) 
solution, I think - unless you have some reason for using an intermediate 
textbox.  I added the code to fill in the Address1, 2, 3, and City when the 
combo is updated: 
 
Private Sub 
cboSelectDeliveryAddress_AfterUpdate()
    Me.txtDeliveryAddress1 = 
Me.cboSelectDeliveryAddress.Column(3)    
Me.txtDeliveryAddress2 = 
Me.cboSelectDeliveryAddress.Column(4)    
Me.txtDeliveryAddress3 = 
Me.cboSelectDeliveryAddress.Column(5)    Me.txtDeliveryCity = 
Me.cboSelectDeliveryAddress.Column(6)
End Sub
 
In order to successfully reference columns in a combo, 
you need to set the column count correctly (I changed it to 7) and specify a 
width for each column - even if it's zero (0).  
 

Tom Oakes Personal PC Consultants, Inc. [EMAIL PROTECTED] 503.230.0911 (O) 402.968.6946 
(C) 713.583.7091 (F) 

 


From: AccessDevelopers@yahoogroups.com 
[mailto:[EMAIL PROTECTED] On Behalf Of 
jezmo_codpieceSent: Wednesday, July 13, 2005 10:11 
AMTo: AccessDevelopers@yahoogroups.comSubject: 
[AccessDevelopers] Re: Can you check this code please
Hi Toby, when I change the selection in the combo box, the 
addressID field changes, but the address fields (just city for test purposs 
at the moment) does not get filled. I have posted the file  
'QuotationDB.zip' if you are able to look at it. The problem form is 
frmquote. Many thnaks.--- In AccessDevelopers@yahoogroups.com, "Toby 
Bierly" <[EMAIL PROTECTED]> wrote:> 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: 
<AccessDevelopers@yahoogroups.com>> 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 
AccessDevelopers@yahoogroups.com, "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: 
AccessDevelopers@yahoogroups.com> > > 
[mailto:[EMAIL PROTECTED] On Behalf Of> > 
jezmo_codpiece> > > Sent: Wednesday, July 13, 2005 9:19 AM> 
> > 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> > >> > >> > >> > > * 
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. Please zip all 
files prior to uploading to Files section. 



Please zip all files prior to uploading to Files section.



  
  





  
  
  YAHOO! GROUPS LINKS



   Visit your group "AccessDevelopers" on the web. 
   To unsubscribe from this group, send an email to: [EMAIL PROTECTED] 
   Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.