DLookup

C

crtopher

I have been following Martin Greene's advice here:

http://www.fontstuff.com/access/acctut10.htm

regarding cascading lists.

I have two combo boxes on a form, cboCategory and cboMainItem. I want
cboMainItem to take it's rowsource from cboCategory, afterUpdate of the
former. I have followed Martin's advice to the letter, and can get the
DLookup working perfectly when the cboMainItem.Value is a text field. If i
make it lookup a number, as in MainID, which is also an autoNumber, the
DLookup doesn't work, ie, the cboCategory doesn't update with each new record
where the cboMainItem is different.

My current code is this:

Private Sub Form_Current()
On Error Resume Next
cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] ='" &
cboMainItem.Value & "'")
cboMainItem.RowSource = "SELECT lkupMainItem.MainID " & _
"FROM lkupMainItem " & _
"WHERE lkupMainItem.Category = '" & cboCategory.Value & "' " & _
"ORDER BY lkupMainItem.MainIN;"
End Sub

Is there a problem using numbers or Autonumbers wirh DLookup??

Much appreciated
Chris
 
M

mscertified

DLookup returns a variant and you need to test for returned Nulls.
Then if not null, you need to convert the variant to whatever you are
expecting e.g.
If isnull(varX) Then
msgbox "Error"
Else
lngNumber = CLng(varX)
end if

-Dorian
 
F

fredg

I have been following Martin Greene's advice here:

http://www.fontstuff.com/access/acctut10.htm

regarding cascading lists.

I have two combo boxes on a form, cboCategory and cboMainItem. I want
cboMainItem to take it's rowsource from cboCategory, afterUpdate of the
former. I have followed Martin's advice to the letter, and can get the
DLookup working perfectly when the cboMainItem.Value is a text field. If i
make it lookup a number, as in MainID, which is also an autoNumber, the
DLookup doesn't work, ie, the cboCategory doesn't update with each new record
where the cboMainItem is different.

My current code is this:

Private Sub Form_Current()
On Error Resume Next
cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] ='" &
cboMainItem.Value & "'")
cboMainItem.RowSource = "SELECT lkupMainItem.MainID " & _
"FROM lkupMainItem " & _
"WHERE lkupMainItem.Category = '" & cboCategory.Value & "' " & _
"ORDER BY lkupMainItem.MainIN;"
End Sub

Is there a problem using numbers or Autonumbers wirh DLookup??

Much appreciated
Chris
Click to expand...

There is no problem using numbers/autonumbers in a dlookup.
The problem is you are using the where clause syntax of a text
datatype field. A Text value must be enclosed within quotes. A Number
datatype must NOT be enclosed within quotes.

If the bound column of cboMainItem is a Number datatype, try:

cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] = " &
cboMainItem)

You'll want to make a similar change in the Row source Where clause
as well.

WHERE lkupMainItem.Category = " & cboCategory & " ORDER BY
lkupMainItem.MainIN;"

Look up Where clause + Restrict data to a subset of records
in VBA help.
..
 
P

Pat Hartman \(MVP\)

I disagree strongly with the method Mr. Greene is suggesting. It requires
you to denormalize your schema in order to create separate tables based on a
data field. It also requires code. The "real" simplest method is to
reference a form field in your combo/listbox RowSource query:

Select ....
From YourTable
Where TheKey = Forms!frmYourForm!TheKeyControlName;

Note that in each RowSource the "TheKeyControlName" will be the name of the
most immediate parent control. The top level parent has no where clause
(unless it needs one for a different reason). Then in the AfterUpdate event
of the parent control, requery the next combo/listbox in the hierarchy:

Me.cboChildx.Requery

That's it!!!!! a single line of code in the combo/listbox's AfterUpdate
event. In some cases, you will also need to requery the combo/listbox in
the form's Current event:
Me.cboParent.Requery
Me.cboChild1.Requery
Me.cboChild2.Requery
 
C

crtopher

That's it. It works perfectly. Thanks FredG

fredg said:
I have been following Martin Greene's advice here:

http://www.fontstuff.com/access/acctut10.htm

regarding cascading lists.

I have two combo boxes on a form, cboCategory and cboMainItem. I want
cboMainItem to take it's rowsource from cboCategory, afterUpdate of the
former. I have followed Martin's advice to the letter, and can get the
DLookup working perfectly when the cboMainItem.Value is a text field. If i
make it lookup a number, as in MainID, which is also an autoNumber, the
DLookup doesn't work, ie, the cboCategory doesn't update with each new record
where the cboMainItem is different.

My current code is this:

Private Sub Form_Current()
On Error Resume Next
cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] ='" &
cboMainItem.Value & "'")
cboMainItem.RowSource = "SELECT lkupMainItem.MainID " & _
"FROM lkupMainItem " & _
"WHERE lkupMainItem.Category = '" & cboCategory.Value & "' " & _
"ORDER BY lkupMainItem.MainIN;"
End Sub

Is there a problem using numbers or Autonumbers wirh DLookup??

Much appreciated
Chris
Click to expand...

There is no problem using numbers/autonumbers in a dlookup.
The problem is you are using the where clause syntax of a text
datatype field. A Text value must be enclosed within quotes. A Number
datatype must NOT be enclosed within quotes.

If the bound column of cboMainItem is a Number datatype, try:

cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] = " &
cboMainItem)

You'll want to make a similar change in the Row source Where clause
as well.

WHERE lkupMainItem.Category = " & cboCategory & " ORDER BY
lkupMainItem.MainIN;"

Look up Where clause + Restrict data to a subset of records
in VBA help.
..
Click to expand...
 
C

crtopher

OK Pat i have simplified things as you suggest and made the row source of my
child combo the following:

SELECT lkupMainItem.Description, lkupMainItem.Category FROM lkupMainItem
WHERE (((lkupMainItem.Category)=forms!form1!cboCategory)) ORDER BY
lkupMainItem.Description;

which works great and was a bit simpler to create.

also i put this in the after update of the parent:

Private Sub cboCategory_AfterUpdate()
On Error Resume Next
Me.cboMainItem.Requery

End Sub

but that didn't seem to do anything...i still had to use DLookup in the
forms current event to get the parent box to reflect the child box as i flick
through the records.

i'm wondering what i'm doing wrong there...do you have to separately
designate somwhere which control is the parent and which the child?

Thanks for your time
Chris


Pat Hartman (MVP) said:
I disagree strongly with the method Mr. Greene is suggesting. It requires
you to denormalize your schema in order to create separate tables based on a
data field. It also requires code. The "real" simplest method is to
reference a form field in your combo/listbox RowSource query:

Select ....
From YourTable
Where TheKey = Forms!frmYourForm!TheKeyControlName;

Note that in each RowSource the "TheKeyControlName" will be the name of the
most immediate parent control. The top level parent has no where clause
(unless it needs one for a different reason). Then in the AfterUpdate event
of the parent control, requery the next combo/listbox in the hierarchy:

Me.cboChildx.Requery

That's it!!!!! a single line of code in the combo/listbox's AfterUpdate
event. In some cases, you will also need to requery the combo/listbox in
the form's Current event:
Me.cboParent.Requery
Me.cboChild1.Requery
Me.cboChild2.Requery

crtopher said:
I have been following Martin Greene's advice here:

http://www.fontstuff.com/access/acctut10.htm

regarding cascading lists.

I have two combo boxes on a form, cboCategory and cboMainItem. I want
cboMainItem to take it's rowsource from cboCategory, afterUpdate of the
former. I have followed Martin's advice to the letter, and can get the
DLookup working perfectly when the cboMainItem.Value is a text field. If i
make it lookup a number, as in MainID, which is also an autoNumber, the
DLookup doesn't work, ie, the cboCategory doesn't update with each new
record
where the cboMainItem is different.

My current code is this:

Private Sub Form_Current()
On Error Resume Next
cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] ='" &
cboMainItem.Value & "'")
cboMainItem.RowSource = "SELECT lkupMainItem.MainID " & _
"FROM lkupMainItem " & _
"WHERE lkupMainItem.Category = '" & cboCategory.Value & "' " & _
"ORDER BY lkupMainItem.MainIN;"
End Sub

Is there a problem using numbers or Autonumbers wirh DLookup??

Much appreciated
Chris
Click to expand...
Click to expand...
 
P

Pat Hartman \(MVP\)

Sorry, I've been away at the MVP summit. Make sure that you are requerying
the child combo. If you have three combos - A, B, and C then in the
AfterUpdate event of A you requery B and C. In the AfterUpdate event of B
you requery C.
crtopher said:
OK Pat i have simplified things as you suggest and made the row source of
my
child combo the following:

SELECT lkupMainItem.Description, lkupMainItem.Category FROM lkupMainItem
WHERE (((lkupMainItem.Category)=forms!form1!cboCategory)) ORDER BY
lkupMainItem.Description;

which works great and was a bit simpler to create.

also i put this in the after update of the parent:

Private Sub cboCategory_AfterUpdate()
On Error Resume Next
Me.cboMainItem.Requery

End Sub

but that didn't seem to do anything...i still had to use DLookup in the
forms current event to get the parent box to reflect the child box as i
flick
through the records.

i'm wondering what i'm doing wrong there...do you have to separately
designate somwhere which control is the parent and which the child?

Thanks for your time
Chris


Pat Hartman (MVP) said:
I disagree strongly with the method Mr. Greene is suggesting. It
requires
you to denormalize your schema in order to create separate tables based
on a
data field. It also requires code. The "real" simplest method is to
reference a form field in your combo/listbox RowSource query:

Select ....
From YourTable
Where TheKey = Forms!frmYourForm!TheKeyControlName;

Note that in each RowSource the "TheKeyControlName" will be the name of
the
most immediate parent control. The top level parent has no where clause
(unless it needs one for a different reason). Then in the AfterUpdate
event
of the parent control, requery the next combo/listbox in the hierarchy:

Me.cboChildx.Requery

That's it!!!!! a single line of code in the combo/listbox's AfterUpdate
event. In some cases, you will also need to requery the combo/listbox in
the form's Current event:
Me.cboParent.Requery
Me.cboChild1.Requery
Me.cboChild2.Requery

crtopher said:
I have been following Martin Greene's advice here:

http://www.fontstuff.com/access/acctut10.htm

regarding cascading lists.

I have two combo boxes on a form, cboCategory and cboMainItem. I want
cboMainItem to take it's rowsource from cboCategory, afterUpdate of the
former. I have followed Martin's advice to the letter, and can get the
DLookup working perfectly when the cboMainItem.Value is a text field.
If i
make it lookup a number, as in MainID, which is also an autoNumber, the
DLookup doesn't work, ie, the cboCategory doesn't update with each new
record
where the cboMainItem is different.

My current code is this:

Private Sub Form_Current()
On Error Resume Next
cboCategory = DLookup("[Category]", "lkupMainItem", "[MainID] ='" &
cboMainItem.Value & "'")
cboMainItem.RowSource = "SELECT lkupMainItem.MainID " & _
"FROM lkupMainItem " & _
"WHERE lkupMainItem.Category = '" & cboCategory.Value & "' " & _
"ORDER BY lkupMainItem.MainIN;"
End Sub

Is there a problem using numbers or Autonumbers wirh DLookup??

Much appreciated
Chris
Click to expand...
Click to expand...
Click to expand...
 

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

Cascading Lists 4
DLookup, how to format vba code 7
Default value of combo box based on another that uses DLookUp - continued 1
Use Multi-Select List boxes as query parameters 6
Long-winded question about limiting choices displayed in combo box 0

Top