fields in dlookup not filling in

K

Karen

I'm using a dlookup to an SQL table and when I enter a customer number in my
form the dlookup returns the customers name and address. But not all of the
fields are getting filled in initially. The name, city and zip always fill
in but the address lines and the state don't until i do something to refresh
the screen, like press F7 which does something like spell check. I'm using
Access 2002, most of the tables are in Access except this arcusfil that's a
link to an SQL database that's on the same server but a different drive.

here's the code:

Dim varcustnumber As String
Dim varbillname As Variant
Dim varadd1 As Variant
Dim varadd2 As Variant
Dim varcity As Variant
Dim varstate As Variant
Dim varzip As Variant
Dim varcountry As Variant


Dim varzeros As String
varzeros = "000000000000"

Dim varlen As Integer
varlen = 12 - Len([CUSTNO])

varcustnumber = Mid(varzeros, 1, varlen) & Me![CUSTNO]


varbillname = DLookup("[cus_name]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd1 = DLookup("[addr_1]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd2 = DLookup("[addr_2]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varcity = DLookup("[city]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varstate = DLookup("[state]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varzip = DLookup("[zip]", "dbo_arcusfil_SQL", "[cus_no] = '" & varcustnumber
& "'")
varcountry = DLookup("[country]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")

Me!billname = Left(varbillname, 30)
Me!ADD1 = Left(varadd1, 30)
Me!ADD2 = Left(varadd2, 30)
Me!CITY = Left(varcity, 25)
Me!ST = Left(varstate, 2)
Me!ZIP = varzip
Me!COUNTRY = Left(varcountry, 15)
 
K

Karen

i have found a stupid fix to this. I'm using Sendkeys "{F9}". F9 is
apparently a refresh the lookup fields.

I would really like to know a real fix or reason for this problem if anyone
can help.

Karen
 
T

tina

hmmm, you could try adding

Me.Repaint

after the last line of your code.

hth


Karen said:
i have found a stupid fix to this. I'm using Sendkeys "{F9}". F9 is
apparently a refresh the lookup fields.

I would really like to know a real fix or reason for this problem if anyone
can help.

Karen



Karen said:
I'm using a dlookup to an SQL table and when I enter a customer number
Click to expand...
in
my
form the dlookup returns the customers name and address. But not all of the
fields are getting filled in initially. The name, city and zip always fill
in but the address lines and the state don't until i do something to refresh
the screen, like press F7 which does something like spell check. I'm using
Access 2002, most of the tables are in Access except this arcusfil
Click to expand...
that's
a
link to an SQL database that's on the same server but a different drive.

here's the code:

Dim varcustnumber As String
Dim varbillname As Variant
Dim varadd1 As Variant
Dim varadd2 As Variant
Dim varcity As Variant
Dim varstate As Variant
Dim varzip As Variant
Dim varcountry As Variant


Dim varzeros As String
varzeros = "000000000000"

Dim varlen As Integer
varlen = 12 - Len([CUSTNO])

varcustnumber = Mid(varzeros, 1, varlen) & Me![CUSTNO]


varbillname = DLookup("[cus_name]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd1 = DLookup("[addr_1]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd2 = DLookup("[addr_2]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varcity = DLookup("[city]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varstate = DLookup("[state]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varzip = DLookup("[zip]", "dbo_arcusfil_SQL", "[cus_no] = '" & varcustnumber
& "'")
varcountry = DLookup("[country]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")

Me!billname = Left(varbillname, 30)
Me!ADD1 = Left(varadd1, 30)
Me!ADD2 = Left(varadd2, 30)
Me!CITY = Left(varcity, 25)
Me!ST = Left(varstate, 2)
Me!ZIP = varzip
Me!COUNTRY = Left(varcountry, 15)
Click to expand...
Click to expand...
 
K

Karen

Thanks for the suggestion Tina, but repaint didn't do the trick. Sendkeys
works better. I'm just not sure that's the only way or the best way to do
this.

Karen

tina said:
hmmm, you could try adding

Me.Repaint

after the last line of your code.

hth


Karen said:
i have found a stupid fix to this. I'm using Sendkeys "{F9}". F9 is
apparently a refresh the lookup fields.

I would really like to know a real fix or reason for this problem if anyone
can help.

Karen



Karen said:
I'm using a dlookup to an SQL table and when I enter a customer number
Click to expand...
in
my
form the dlookup returns the customers name and address. But not all
Click to expand...
of
the
fields are getting filled in initially. The name, city and zip always fill
in but the address lines and the state don't until i do something to refresh
the screen, like press F7 which does something like spell check. I'm using
Access 2002, most of the tables are in Access except this arcusfil
Click to expand...
that's
a
link to an SQL database that's on the same server but a different drive.

here's the code:

Dim varcustnumber As String
Dim varbillname As Variant
Dim varadd1 As Variant
Dim varadd2 As Variant
Dim varcity As Variant
Dim varstate As Variant
Dim varzip As Variant
Dim varcountry As Variant


Dim varzeros As String
varzeros = "000000000000"

Dim varlen As Integer
varlen = 12 - Len([CUSTNO])

varcustnumber = Mid(varzeros, 1, varlen) & Me![CUSTNO]


varbillname = DLookup("[cus_name]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd1 = DLookup("[addr_1]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd2 = DLookup("[addr_2]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varcity = DLookup("[city]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varstate = DLookup("[state]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varzip = DLookup("[zip]", "dbo_arcusfil_SQL", "[cus_no] = '" & varcustnumber
& "'")
varcountry = DLookup("[country]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")

Me!billname = Left(varbillname, 30)
Me!ADD1 = Left(varadd1, 30)
Me!ADD2 = Left(varadd2, 30)
Me!CITY = Left(varcity, 25)
Me!ST = Left(varstate, 2)
Me!ZIP = varzip
Me!COUNTRY = Left(varcountry, 15)
Click to expand...
Click to expand...
Click to expand...
 
T

tina

well, the general rule-of-thumb is to avoid using SendKeys. but in the
absence of another solution that works, i can't really advise you to do
that. perhaps somebody else will suggest a better solution. good luck.


Karen said:
Thanks for the suggestion Tina, but repaint didn't do the trick. Sendkeys
works better. I'm just not sure that's the only way or the best way to do
this.

Karen

tina said:
hmmm, you could try adding

Me.Repaint

after the last line of your code.

hth


number
in
Click to expand...
all
of
the
fields are getting filled in initially. The name, city and zip always
fill
in but the address lines and the state don't until i do something to
refresh
the screen, like press F7 which does something like spell check. I'm
using
Access 2002, most of the tables are in Access except this arcusfil that's
a
link to an SQL database that's on the same server but a different drive.

here's the code:

Dim varcustnumber As String
Dim varbillname As Variant
Dim varadd1 As Variant
Dim varadd2 As Variant
Dim varcity As Variant
Dim varstate As Variant
Dim varzip As Variant
Dim varcountry As Variant


Dim varzeros As String
varzeros = "000000000000"

Dim varlen As Integer
varlen = 12 - Len([CUSTNO])

varcustnumber = Mid(varzeros, 1, varlen) & Me![CUSTNO]


varbillname = DLookup("[cus_name]", "dbo_arcusfil_SQL", "[cus_no] =
Click to expand...
Click to expand...
'"
&
varcustnumber & "'")
varadd1 = DLookup("[addr_1]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varadd2 = DLookup("[addr_2]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varcity = DLookup("[city]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varstate = DLookup("[state]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")
varzip = DLookup("[zip]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber
& "'")
varcountry = DLookup("[country]", "dbo_arcusfil_SQL", "[cus_no] = '" &
varcustnumber & "'")

Me!billname = Left(varbillname, 30)
Me!ADD1 = Left(varadd1, 30)
Me!ADD2 = Left(varadd2, 30)
Me!CITY = Left(varcity, 25)
Me!ST = Left(varstate, 2)
Me!ZIP = varzip
Me!COUNTRY = Left(varcountry, 15)
Click to expand...
Click to expand...
Click to expand...
 
K

Karen

I found out why this was happening. I was reading a post about a calendar
not updating a field until a new record was created and it made sense that
this was my problem...

Allen Browne wrote:
If the record does not show the new date until it is saved, my guess is that
the text box has a different name than the field it is bound to, and the
calendar is updating the *field*, not the text box. You could solve that by
changing the name that the calendar updates to the name of the text box
rather than the name of the field, or rename the text box so it has the same
name as the field.
-end quote-

I checked to see if I was using the field names or the controls names and I
was using the field names. I changed to the controls names and now my
controls are updated as I wanted them to.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

please help debug 4
Fill Fields automatically on form based on a control's value 1
how to auto populate a field from another table using dlookup 5
Q. Zipcode table. Can someone please explain this to me? 1
Forms:Fill Fields automatically 1
Repost - Forms Fill Fields automatically on form based on a control's value 5
Combo box that automatically update a sub-form record 0
type mismatch error in macro in excel 0

Top