Dlookup in a form

  • Thread starter Thread starter Patti
  • Start date Start date
P

Patti

Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup("[Project]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes, the
rowsourcetype is set to Table/query and the row source is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE.[Dept]

What am I doing wrong?
 
Patti, Instead of doing a DLookUp, since you already have a query/sql statement that will return the
results you want just requery those controls in the AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.
 
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?
-----Original Message-----
Patti, Instead of doing a DLookUp, since you already have
Click to expand...
a query/sql statement that will return the
results you want just requery those controls in the AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup("[Project]", "tblRFCE", "[RFCE] =" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes, the
rowsourcetype is set to Table/query and the row source is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE. [Dept]

What am I doing wrong?
Click to expand...


.
Click to expand...
 
Well I just got it to work. I set the bound column to 1
on cmbRFCE. That did the trick but I don't know why! I
haven't quite figured out what "bound columns" means!
-----Original Message-----
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?
-----Original Message-----
Patti, Instead of doing a DLookUp, since you already
Click to expand...
have
a query/sql statement that will return the
results you want just requery those controls in the AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup("[Project]", "tblRFCE", "[RFCE] =" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes, the
rowsourcetype is set to Table/query and the row source is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE. [Dept]

What am I doing wrong?
Click to expand...


.
Click to expand...
.
Click to expand...
 
The original way you did dLookup refers to a data type
of "number" not "string". So, if the bound field of
combo box you are referring to is a number, then it will
work. If it is not, you have to add some "'"
(apsotrophes) to you code like this:

[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]='" &
[Forms]![PO]![cmbRFCE] & "'")


-----Original Message-----
Well I just got it to work. I set the bound column to 1
on cmbRFCE. That did the trick but I don't know why! I
haven't quite figured out what "bound columns" means!
-----Original Message-----
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?
-----Original Message-----
Patti, Instead of doing a DLookUp, since you already
Click to expand...
have
a query/sql statement that will return the
results you want just requery those controls in the AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup
Click to expand...
("[Project]", "tblRFCE", "[RFCE]
=" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo
Click to expand...
boxes,
the
rowsourcetype is set to Table/query and the row
Click to expand...
source
is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE. [Dept]

What am I doing wrong?


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Patti, The bound column is the column that holds the value you want stored in that control/field.
For instance assume you had a combo box on your form and you set the control source of the combo to
a field on your form and the field name is LastName.Therefore when you select a value from your
combo you want to save the last name to this field obviously. Now assume you designed your combobox
to display 2 columns when viewed, like FirstName and LastName and that they displayed that way from
left to right. When the user makes a selection you want the value in column 2(LastName) to be saved
in this field. Therefore you would set the bound column to 2. So basically, whatever value you
want to retrieve from the combo, regardless of how many columns are displayed, just set the bound
column = to the column number that holds that value. Also, you can hide columns by setting the
width to 0. So if you had a combo listing your employees and the employees table has a primary key
field that you want to store, but not show to the user, you could for example, build a query from
the employees table with the PK field first, then empLastName and empFirstName. Now set the
following properties for the combo box

Row Source = YourQuery
Column Count = 3
Column Widths = 0";1";1"
Bound column = 1

Hope this helps!

--
Reggie

----------
Patti said:
Well I just got it to work. I set the bound column to 1
on cmbRFCE. That did the trick but I don't know why! I
haven't quite figured out what "bound columns" means!
-----Original Message-----
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?
-----Original Message-----
Patti, Instead of doing a DLookUp, since you already
Click to expand...
have
a query/sql statement that will return the
results you want just requery those controls in the AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup("[Project]", "tblRFCE", "[RFCE] =" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes, the
rowsourcetype is set to Table/query and the row source is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE. [Dept]

What am I doing wrong?


.
Click to expand...
.
Click to expand...
Click to expand...
 
Yes that helps! Thanks so much for the explanation!
-----Original Message-----
Patti, The bound column is the column that holds the
Click to expand...
value you want stored in that control/field.
For instance assume you had a combo box on your form and
Click to expand...
you set the control source of the combo to
a field on your form and the field name is
Click to expand...
LastName.Therefore when you select a value from your
combo you want to save the last name to this field
Click to expand...
obviously. Now assume you designed your combobox
to display 2 columns when viewed, like FirstName and
Click to expand...
LastName and that they displayed that way from
left to right. When the user makes a selection you want
Click to expand...
the value in column 2(LastName) to be saved
in this field. Therefore you would set the bound column
Click to expand...
to 2. So basically, whatever value you
want to retrieve from the combo, regardless of how many
Click to expand...
columns are displayed, just set the bound
column = to the column number that holds that value.
Click to expand...
Also, you can hide columns by setting the
width to 0. So if you had a combo listing your employees
Click to expand...
and the employees table has a primary key
field that you want to store, but not show to the user,
Click to expand...
you could for example, build a query from
the employees table with the PK field first, then
Click to expand...
empLastName and empFirstName. Now set the
following properties for the combo box

Row Source = YourQuery
Column Count = 3
Column Widths = 0";1";1"
Bound column = 1

Hope this helps!

--
Reggie

----------
Well I just got it to work. I set the bound column to 1
on cmbRFCE. That did the trick but I don't know why! I
haven't quite figured out what "bound columns" means!
-----Original Message-----
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?

-----Original Message-----
Patti, Instead of doing a DLookUp, since you already have
a query/sql statement that will return the
results you want just requery those controls in the
AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
message
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup ("[Project]", "tblRFCE", "[RFCE]
=" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes,
the
rowsourcetype is set to Table/query and the row source
is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE.
[Dept]

What am I doing wrong?


.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks!
-----Original Message-----
The original way you did dLookup refers to a data type
of "number" not "string". So, if the bound field of
combo box you are referring to is a number, then it will
work. If it is not, you have to add some "'"
(apsotrophes) to you code like this:

[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]='" &
[Forms]![PO]![cmbRFCE] & "'")


-----Original Message-----
Well I just got it to work. I set the bound column to 1
on cmbRFCE. That did the trick but I don't know why! I
haven't quite figured out what "bound columns" means!
-----Original Message-----
Hi Reggie,

It's still not working. No error, but no data -- just
headers! Since it's pulling the headers, I assume it's
reading the right field in the table, but it's not
recognizing the matching field with cmbRFCE. Any ideas?

-----Original Message-----
Patti, Instead of doing a DLookUp, since you already have
a query/sql statement that will return the
results you want just requery those controls in the
AfterUpdate event:

Private Sub cmbRFCE__AfterUpdate()
Me.[cmbProject].Requery
Me.[cmbDept] .Requery
End sub

Hope I understood correctly and that this helps.

--
Reggie

----------
message
Hi!

I'm trying to autofill a form using Dlookup and it's not
working. The controlling field is cmbRFCE. In the
AfterUpdate field, I have the following:

Private Sub cmbRFCE__AfterUpdate()
[cmbProject] = DLookup ("[Project]", "tblRFCE", "[RFCE]
=" &
[Forms]![PO]![cmbRFCE])
[cmbDept] = DLookup("[Dept]", "tblRFCE", "[RFCE]=" &
[Forms]![PO]![cmbRFCE])

End Sub

It doesn't give me any errors, but the cmbProject and
cmbDept fields don't populate. In those combo boxes,
the
rowsourcetype is set to Table/query and the row source
is
a query: SELECT tblRFCE.[Project] FROM tblRFCE WHERE
(((tblRFCE.[RFCE])=[Forms]![PO]![cmbRFCE])); The Dept
combo box has the same query but it selects tblRFCE.
[Dept]

What am I doing wrong?


.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top