Fill fields automatically

[helenp]

I've been trying to work my way through something I got
from the Access Web, code to automatically fill in
certain fields on a form based on the value placed in
another field. I'm getting a hazy idea of how the process
should work, but I'm struggling with some of the
specifics. This is the sample code:

Sub Zip_OnExit(Cancel As Integer)
Dim varState As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =
[Zip]")
If (Not IsNull(varState)) Then Me![State] = varState
End Sub

I think the part that's tripping me up is how the first
and third elements in the DLookup statement relate to my
own tables and fields. I'm not sure what they refer to:
fields/labels/whatever on the form, in the lookup table,
in the destination table, or resort spas on Mars. This is
all new to me, so any help in deciphering this would be
most appreciated...

Thanks,
Helen

 

[DebbieG]

The first argument of DLookUp is the field that has what you want to return.
The second argument is the name of the table that contains the field from the
first argument.
The third argument is a Where: Where field in the table (second argument) =
(in this case) a field on your form.

Sub Zip_OnExit(Cancel As Integer)
Dim varState As Variant
varState = DLookup("[State]", "tblZipCode", "[ZipCode] = " & Me.Zip)
If Not IsNull(varState) Then Me.State = varState
End Sub

This assumes that Zip is a numeric field. If it is a text field change to:

varState = DLookup("[State]", "tblZipCode", "[ZipCode] = ' " & Me.Zip & " ' " )

HTH,
Debbie


| I've been trying to work my way through something I got
| from the Access Web, code to automatically fill in
| certain fields on a form based on the value placed in
| another field. I'm getting a hazy idea of how the process
| should work, but I'm struggling with some of the
| specifics. This is the sample code:
|
| Sub Zip_OnExit(Cancel As Integer)
| Dim varState As Variant
| varState = DLookup("State", "tblZipCode", "ZipCode =
| [Zip]")
| If (Not IsNull(varState)) Then Me![State] = varState
| End Sub
|
| I think the part that's tripping me up is how the first
| and third elements in the DLookup statement relate to my
| own tables and fields. I'm not sure what they refer to:
| fields/labels/whatever on the form, in the lookup table,
| in the destination table, or resort spas on Mars. This is
| all new to me, so any help in deciphering this would be
| most appreciated...
|
| Thanks,
| Helen

 

[Guest]

Thanks, Debbie. This makes sense now! Unfortunately, it's
still not working for me, so I'm obviously doing
something wrong. I'll keep trying...

Helen

-----Original Message-----
The first argument of DLookUp is the field that has what you want to return.
The second argument is the name of the table that contains the field from the
first argument.
The third argument is a Where: Where field in the table (second argument) =
(in this case) a field on your form.

Sub Zip_OnExit(Cancel As Integer)
Dim varState As Variant
varState = DLookup

("[State]", "tblZipCode", "[ZipCode] = " & Me.Zip)

If Not IsNull(varState) Then Me.State = varState
End Sub

This assumes that Zip is a numeric field. If it is a text field change to:

varState = DLookup("[State]", "tblZipCode", "[ZipCode] = ' " & Me.Zip & " ' " )

HTH,
Debbie


| I've been trying to work my way through something I got
| from the Access Web, code to automatically fill in
| certain fields on a form based on the value placed in
| another field. I'm getting a hazy idea of how the process
| should work, but I'm struggling with some of the
| specifics. This is the sample code:
|
| Sub Zip_OnExit(Cancel As Integer)
| Dim varState As Variant
| varState = DLookup("State", "tblZipCode", "ZipCode =
| [Zip]")
| If (Not IsNull(varState)) Then Me![State] = varState
| End Sub
|
| I think the part that's tripping me up is how the first
| and third elements in the DLookup statement relate to my
| own tables and fields. I'm not sure what they refer to:
| fields/labels/whatever on the form, in the lookup table,
| in the destination table, or resort spas on Mars. This is
| all new to me, so any help in deciphering this would be
| most appreciated...
|
| Thanks,
| Helen


.

 

[DebbieG]

When you states "it's still not working" ... are you getting an error or just
not getting your data?


| Thanks, Debbie. This makes sense now! Unfortunately, it's
| still not working for me, so I'm obviously doing
| something wrong. I'll keep trying...
|
| Helen
|
| >-----Original Message-----
| >The first argument of DLookUp is the field that has what
| you want to return.
| >The second argument is the name of the table that
| contains the field from the
| >first argument.
| >The third argument is a Where: Where field in the table
| (second argument) =
| >(in this case) a field on your form.
| >
| >Sub Zip_OnExit(Cancel As Integer)
| > Dim varState As Variant
| > varState = DLookup
| ("[State]", "tblZipCode", "[ZipCode] = " & Me.Zip)
| > If Not IsNull(varState) Then Me.State = varState
| >End Sub
| >
| >This assumes that Zip is a numeric field. If it is a
| text field change to:
| >
| >varState = DLookup("[State]", "tblZipCode", "[ZipCode]
| = ' " & Me.Zip & " ' " )
| >
| >HTH,
| >Debbie
| >
| >
| message
| >| >| I've been trying to work my way through something I got
| >| from the Access Web, code to automatically fill in
| >| certain fields on a form based on the value placed in
| >| another field. I'm getting a hazy idea of how the
| process
| >| should work, but I'm struggling with some of the
| >| specifics. This is the sample code:
| >|
| >| Sub Zip_OnExit(Cancel As Integer)
| >| Dim varState As Variant
| >| varState = DLookup("State", "tblZipCode", "ZipCode =
| >| [Zip]")
| >| If (Not IsNull(varState)) Then Me![State] = varState
| >| End Sub
| >|
| >| I think the part that's tripping me up is how the first
| >| and third elements in the DLookup statement relate to
| my
| >| own tables and fields. I'm not sure what they refer to:
| >| fields/labels/whatever on the form, in the lookup
| table,
| >| in the destination table, or resort spas on Mars. This
| is
| >| all new to me, so any help in deciphering this would be
| >| most appreciated...
| >|
| >| Thanks,
| >| Helen
| >
| >
| >.
| >

 

[helenp]

No data, no error, no disturbance in the Force.

Helen

-----Original Message-----
When you states "it's still not working" ... are you getting an error or just
not getting your data?


| Thanks, Debbie. This makes sense now! Unfortunately, it's
| still not working for me, so I'm obviously doing
| something wrong. I'll keep trying...
|
| Helen
|
| >-----Original Message-----
| >The first argument of DLookUp is the field that has what
| you want to return.
| >The second argument is the name of the table that
| contains the field from the
| >first argument.
| >The third argument is a Where: Where field in the table
| (second argument) =
| >(in this case) a field on your form.
| >
| >Sub Zip_OnExit(Cancel As Integer)
| > Dim varState As Variant
| > varState = DLookup
| ("[State]", "tblZipCode", "[ZipCode] = " & Me.Zip)
| > If Not IsNull(varState) Then Me.State = varState
| >End Sub
| >
| >This assumes that Zip is a numeric field. If it is a
| text field change to:
| >
| >varState = DLookup("[State]", "tblZipCode", "[ZipCode]
| = ' " & Me.Zip & " ' " )
| >
| >HTH,
| >Debbie
| >
| >
| message
| >| >| I've been trying to work my way through something I got
| >| from the Access Web, code to automatically fill in
| >| certain fields on a form based on the value placed in
| >| another field. I'm getting a hazy idea of how the
| process
| >| should work, but I'm struggling with some of the
| >| specifics. This is the sample code:
| >|
| >| Sub Zip_OnExit(Cancel As Integer)
| >| Dim varState As Variant
| >| varState = DLookup

("State", "tblZipCode", "ZipCode =

| >| [Zip]")
| >| If (Not IsNull(varState)) Then Me![State] = varState
| >| End Sub
| >|
| >| I think the part that's tripping me up is how the first
| >| and third elements in the DLookup statement relate to
| my
| >| own tables and fields. I'm not sure what they refer to:
| >| fields/labels/whatever on the form, in the lookup
| table,
| >| in the destination table, or resort spas on Mars. This
| is
| >| all new to me, so any help in deciphering this would be
| >| most appreciated...
| >|
| >| Thanks,
| >| Helen
| >
| >
| >.
| >


.

 

[DebbieG]

Did you try:

varState = DLookup("[State]", "tblZipCode", "[ZipCode]| = ' " & Me.Zip & " ' " )

What is your form based on ... a table or a query? If a table, what is the
table's name? If a query, what table(s) is used in the query?

Is the name of your field on your form called Zip or ZipCode?

Debbie


| No data, no error, no disturbance in the Force.
|
| Helen
|
| >-----Original Message-----
| >When you states "it's still not working" ... are you
| getting an error or just
| >not getting your data?
| >
| >
| >| >| Thanks, Debbie. This makes sense now! Unfortunately,
| it's
| >| still not working for me, so I'm obviously doing
| >| something wrong. I'll keep trying...
| >|
| >| Helen
| >|
| >| >-----Original Message-----
| >| >The first argument of DLookUp is the field that has
| what
| >| you want to return.
| >| >The second argument is the name of the table that
| >| contains the field from the
| >| >first argument.
| >| >The third argument is a Where: Where field in the
| table
| >| (second argument) =
| >| >(in this case) a field on your form.
| >| >
| >| >Sub Zip_OnExit(Cancel As Integer)
| >| > Dim varState As Variant
| >| > varState = DLookup
| >| ("[State]", "tblZipCode", "[ZipCode] = " & Me.Zip)
| >| > If Not IsNull(varState) Then Me.State = varState
| >| >End Sub
| >| >
| >| >This assumes that Zip is a numeric field. If it is a
| >| text field change to:
| >| >
| >| >varState = DLookup("[State]", "tblZipCode", "[ZipCode]
| >| = ' " & Me.Zip & " ' " )
| >| >
| >| >HTH,
| >| >Debbie
| >| >
| >| >
| in
| >| message
| >| >| >| >| I've been trying to work my way through something I
| got
| >| >| from the Access Web, code to automatically fill in
| >| >| certain fields on a form based on the value placed
| in
| >| >| another field. I'm getting a hazy idea of how the
| >| process
| >| >| should work, but I'm struggling with some of the
| >| >| specifics. This is the sample code:
| >| >|
| >| >| Sub Zip_OnExit(Cancel As Integer)
| >| >| Dim varState As Variant
| >| >| varState = DLookup
| ("State", "tblZipCode", "ZipCode =
| >| >| [Zip]")
| >| >| If (Not IsNull(varState)) Then Me![State] =
| varState
| >| >| End Sub
| >| >|
| >| >| I think the part that's tripping me up is how the
| first
| >| >| and third elements in the DLookup statement relate
| to
| >| my
| >| >| own tables and fields. I'm not sure what they refer
| to:
| >| >| fields/labels/whatever on the form, in the lookup
| >| table,
| >| >| in the destination table, or resort spas on Mars.
| This
| >| is
| >| >| all new to me, so any help in deciphering this
| would be
| >| >| most appreciated...
| >| >|
| >| >| Thanks,
| >| >| Helen
| >| >
| >| >
| >| >.
| >| >
| >
| >
| >.
| >

 

[Guest]

Hmmm... I posted the original sample code verbatim for
simplicity, my project is a little more... messy. I'll
try to be clear what I'm trying to do, but please forgive
clunky grasp of proper notation syntax!

Form is based on tblHCProvider:
value in [HCPClinicGroup] comes from a combo box based on
[qsLookupClinic]![LookupClinicGroup], not limited to list
and may be left blank

On exit [HCPClinicGroup] I want this:
if current form [HCPClinicGroup] = [tblLookupClinicGroup]!
[LookupClinicGroup]THEN
insert [tblLookupClinicGroup]![LookupClinicStreet] into
current form [HCPAddress]

I could also use the query as the lookup source.

This is how the code looks at this point:
Private Sub HCPClinicGroup_OnExit(Cancel As Integer)
'Inserts data into addressfield based on clinic choice
Dim varStreet As Variant
varStreet = DLookup
("[LookupClinicStreet]", "tblLookupClinicGroup", "[LookupC
linicGroup] = ' " & Me.HCPClinicGroup & " ' ")
If (Not IsNull(varStreet)) Then Me.HCPAddress =
varStreet
End Sub

I've tried about 72 different ways of writing it,
including pasting in your sample and then changing the
names to protect the innocent; same result. Way too much
fun. Thanks SO much for your help. There are other fields
to fill in as well, but if I can get one right, I can do
the rest ok.

Helen