DLookup, how to format vba code

[Rick S.]

I am struggling with this. I have asked questions on other forums only to be
told I should do this or do that. But I am trying to learn how to do
something other than some ones typical use of something.
What I am doing is an excersize, alas, not for school.
One of my stumbling points are, helping members are proficiant with code, I
struggle to read some of it. I am quite proficiant in VBA for Excel, these
seem to be different animals allthough they both use VBA.

Using Textboxes (I know, I know, I should use cascading combo boxes).
I enter a value in textbox1, textbox2 should only have choices available per
the data from textbox1.
I think I should use DLookup but am struggling terribly to create correct
syntax. This code is in my BeforeUpdate event:

Private Sub sPartRev_BeforeUpdate(Cancel As Integer)
varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")
MsgBox varX
'Two lines above are new and do not work, below functions properly
If DCount("*", "tblPartRev", "txtRev = '" & Me.sPartRev.Text & "'") Then
bOk = True
SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
Key:="sPartRev", setting:=sPartRev
Else
bOk = False
Cancel = False
MsgBox "The revision level doesn't match part numbers in this database.
Please re-enter the revision level."
End If
End Sub
I get:
Run-time error '3078':
The microsoft Jet database engine cannot find the input table or querry
'txtPartNo'. Make sure it exists and that its name is spelled correctly.

It exists and is spelled correctly.

tblParts is the table used for textbox1
tblPartRev contains data for txtRev (related to tblParts)
txtPartNo is the data from textbox1 (already looked up in tblParts)
sPartRev is the data entered in textbox2

Excluding the two errant lines, the code functions but I get all choices
from tblPartRev instead of choices related to the value from textbox1.

All help is appreocaited.
--
Regards

Rick
NT4.0
Office 2k

 

[Tom van Stiphout]

On Fri, 6 Nov 2009 05:53:01 -0800, Rick S.

You probably have a *control* named txtPartNo, while the DLookup
function is looking for a *field* in the underlying table/query. It is
most likely the value in the ControlSource of txtPartNo.

-Tom.
Microsoft Access MVP

 

[Roger Carlson]

Well, one of the reasons you're meeting such resistance is that by doing
things as you are, you are violating the rules of normalization, which are
foundational to good database application design.

Despite that, the reason you're having problems with your DLookup is that
you've got the syntax wrong. In general, a domain function has the
following syntax:

DLookup("fieldname", "tablename", [where condition])

Your DLookup has the table first, a textbox next, and then the where
condition (which looks wrong). Since the values are in quotes, the DLookup
is literally looking for a table called txtPartNo, which does not exist.
The values in the quotes must be the real names in the real tables.

I'm guessing it should be something like:
DLookup("PartNo", "tblParts", "PartNo ='" & Me.txtPartNo.Text & "'")

Still, I would advise you to re-think this design as it will continue to
cause you problems along the way.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am struggling with this. I have asked questions on other forums only to
be
told I should do this or do that. But I am trying to learn how to do
something other than some ones typical use of something.
What I am doing is an excersize, alas, not for school.
One of my stumbling points are, helping members are proficiant with code,
I
struggle to read some of it. I am quite proficiant in VBA for Excel, these
seem to be different animals allthough they both use VBA.

Using Textboxes (I know, I know, I should use cascading combo boxes).
I enter a value in textbox1, textbox2 should only have choices available
per
the data from textbox1.
I think I should use DLookup but am struggling terribly to create correct
syntax. This code is in my BeforeUpdate event:

Private Sub sPartRev_BeforeUpdate(Cancel As Integer)
varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")
MsgBox varX
'Two lines above are new and do not work, below functions properly
If DCount("*", "tblPartRev", "txtRev = '" & Me.sPartRev.Text & "'") Then
bOk = True
SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
Key:="sPartRev", setting:=sPartRev
Else
bOk = False
Cancel = False
MsgBox "The revision level doesn't match part numbers in this database.
Please re-enter the revision level."
End If
End Sub
I get:
Run-time error '3078':
The microsoft Jet database engine cannot find the input table or querry
'txtPartNo'. Make sure it exists and that its name is spelled correctly.

It exists and is spelled correctly.

tblParts is the table used for textbox1
tblPartRev contains data for txtRev (related to tblParts)
txtPartNo is the data from textbox1 (already looked up in tblParts)
sPartRev is the data entered in textbox2

Excluding the two errant lines, the code functions but I get all choices
from tblPartRev instead of choices related to the value from textbox1.

All help is appreocaited.
--
Regards

Rick
NT4.0
Office 2k

 

[Beetle]

Comments inline:

I enter a value in textbox1, textbox2 should only have choices available per
the data from textbox1.

A text box doesn't have "choices". A user can enter a value, or you can
display a value. You could use code to build a list of values separated
by, for example, a comma and display it in the text box, but the user
would not be able to "choose"one of the values. Is that what you are
trying to accomplish?

varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")

Your syntax is wrong here. The field name comes first, then the
table name, then the criteria. Plus Dlookup is only going to
return a single row (the first one it finds that meets the
criteria), not a set of records.

If DCount("*", "tblPartRev", "txtRev = '" & Me.sPartRev.Text & "'") Then

This is simply going to count the number of records that
meet the criteria, so I'm not sure how that's going to meet
your needs, plus you probably would need to add >0 at the
end of the statement (If DCount(blah,blah,blah)>0 Then).

I get:
Run-time error '3078':
The microsoft Jet database engine cannot find the input table or querry
'txtPartNo'. Make sure it exists and that its name is spelled correctly.

This is because you have a field name where the table name should
be in the DLookup statement. It is looking for a table named txtPartNo
and can't find it.

If you can provide some more detail about exactly what it is you
want to accomplish, someone may be able to offer more advice
as to how to proceed.

--
_________

Sean Bailey

I am struggling with this. I have asked questions on other forums only to be
told I should do this or do that. But I am trying to learn how to do
something other than some ones typical use of something.
What I am doing is an excersize, alas, not for school.
One of my stumbling points are, helping members are proficiant with code, I
struggle to read some of it. I am quite proficiant in VBA for Excel, these
seem to be different animals allthough they both use VBA.

Using Textboxes (I know, I know, I should use cascading combo boxes).
I enter a value in textbox1, textbox2 should only have choices available per
the data from textbox1.
I think I should use DLookup but am struggling terribly to create correct
syntax. This code is in my BeforeUpdate event:

Private Sub sPartRev_BeforeUpdate(Cancel As Integer)
varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")
MsgBox varX
'Two lines above are new and do not work, below functions properly
If DCount("*", "tblPartRev", "txtRev = '" & Me.sPartRev.Text & "'") Then
bOk = True
SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
Key:="sPartRev", setting:=sPartRev
Else
bOk = False
Cancel = False
MsgBox "The revision level doesn't match part numbers in this database.
Please re-enter the revision level."
End If
End Sub
I get:
Run-time error '3078':
The microsoft Jet database engine cannot find the input table or querry
'txtPartNo'. Make sure it exists and that its name is spelled correctly.

It exists and is spelled correctly.

tblParts is the table used for textbox1
tblPartRev contains data for txtRev (related to tblParts)
txtPartNo is the data from textbox1 (already looked up in tblParts)
sPartRev is the data entered in textbox2

Excluding the two errant lines, the code functions but I get all choices
from tblPartRev instead of choices related to the value from textbox1.

All help is appreocaited.
--
Regards

Rick
NT4.0
Office 2k

 

[Rick S.]

Bear with me, I will explain what I know.

A text box doesn't have "choices". A user can enter a value, or you can
display a value. You could use code to build a list of values separated
by, for example, a comma and display it in the text box, but the user
would not be able to "choose"one of the values. Is that what you are
trying to accomplish?

The DCount is the list based on the tblParts table. Any entry not mtaching
tblParts entries is redirected back to the user to re-enter the value.

I wish to accomplish:
If the value in sPartNumber (textbox1) is in tblParts (txtPartNo) then the
value entered in sPartRev (textbox2) should evaluate to tblParts and
tblPartRev (txtRev).

This is simply going to count the number of records that
meet the criteria, so I'm not sure how that's going to meet
your needs, plus you probably would need to add >0 at the
end of the statement (If DCount(blah,blah,blah)>0 Then).

I will add ">0" as suggested.

varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")

Your syntax is wrong here. The field name comes first, then the
table name, then the criteria. Plus Dlookup is only going to
return a single row (the first one it finds that meets the
criteria), not a set of records.

Hmm. I guess I need a "record set"?

I have this posted on another site, it hasn't had any activity which is why
Ihave tried this site.
http://www.access-programmers.co.uk/forums/showthread.php?t=182566
It has a small db attached in ZIP format.

All help is appreciated.

--
Regards

Rick
NT4.0
Office 2k

 

[Beetle]

I took a look at your db. I didn't spend much time examining
all of your relationships etc., but I did see enough to realize
at least part of what your issue is. For now I'll limit this post
to just tblParts and tblPartRev which are related via pkPartID.
You can apply this same logic to your other procedures for
data from tables further down the relationship chain.

First, a couple of notes.

1)When using an event that has a built in Cancel event
(BeforeUpdate, BeforeInsert, etc.), if you do want to
cancel the update you set the Cancel argument to True
(not False as you currently have in your code).

2)I don't think your boolean variable – bOK – is even
necessary, so I left it out of this example. You appear to
using it to try and control what happens in the AfterUpdate
event, but if you cancel the BeforeUpdate event properly the
AfterUpdate event never fires, and focus never leaves the
control in question.

3)I may have some of your field/table names slightly wrong.

So, on your unbound form when a user enters a part number in the
sPartNumber text box, you need to see if the value exists in tblParts.
Simple enough;

Private Sub sPartNumber_BeforeUpdate (Cancel As Integer)

'Lookup the value in the table using Dlookup.
'Dlookup will return Null if the value is not found so
'we check for that using the IsNull function.

If IsNull(DLookup(“txtPartNoâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)) Then
'No match found. Cancel the update and display a message.
Cancel = True
MsgBox “The part number you entered was not found.â€
Else
SaveSetting:' blah, blah, whatever you code was for
'saving to the registry
End If

End Sub

Next, the user enters a revision value and you need to check tblPartRev
to see if the value entered exists for the part number entered previously.
However, the part number doesn't exist in tblPartRev, the part numbers
PK value does (in the fkPartID field). So you have to get that part
numbers PK value first, then use it as part of the criteria in another
Dlookup statement;

Private Sub sPartRev_BeforeUpdate (Cancel As integer)

'Declare a variable to store the PK value
Dim lngID As Long

'Get the Pk value of the part number in sPartNumber
lngID = Dlookup(“pkPartIDâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)

'Now check if the revision value entered exists for that
'part numbers PK value in tblPartRev

If IsNull(DLookup(“txtRevâ€, “tblPartRevâ€, “txtRev='†_
& Me.sPartRev & “' And fkPartID=†& lngID)) Then
'No match was found
Cancel = True
MsgBox “The revision value entered does not exist for this part number.â€
Else
'your SaveSetting code
End If

End Sub

There are other ways this could be accomplished as well, using
recordsets, etc., but the general idea would be the same. As you
move through the relationship chain from table to table, you have
to get the PK value of the previously entered value and use that
in your criteria.
--
_________

Sean Bailey

Bear with me, I will explain what I know.

A text box doesn't have "choices". A user can enter a value, or you can
display a value. You could use code to build a list of values separated
by, for example, a comma and display it in the text box, but the user
would not be able to "choose"one of the values. Is that what you are
trying to accomplish?

The DCount is the list based on the tblParts table. Any entry not mtaching
tblParts entries is redirected back to the user to re-enter the value.

I wish to accomplish:
If the value in sPartNumber (textbox1) is in tblParts (txtPartNo) then the
value entered in sPartRev (textbox2) should evaluate to tblParts and
tblPartRev (txtRev).

This is simply going to count the number of records that
meet the criteria, so I'm not sure how that's going to meet
your needs, plus you probably would need to add >0 at the
end of the statement (If DCount(blah,blah,blah)>0 Then).

I will add ">0" as suggested.

varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")

Your syntax is wrong here. The field name comes first, then the
table name, then the criteria. Plus Dlookup is only going to
return a single row (the first one it finds that meets the
criteria), not a set of records.

Hmm. I guess I need a "record set"?

I have this posted on another site, it hasn't had any activity which is why
Ihave tried this site.
http://www.access-programmers.co.uk/forums/showthread.php?t=182566
It has a small db attached in ZIP format.

All help is appreciated.

--
Regards

Rick
NT4.0
Office 2k

 

[Rick S.]

Thank you Beetle for your time and well explained information!
This will definately help me understand some things.
Again, thank you.

--
Regards

Rick
NT4.0
Office 2k

I took a look at your db. I didn't spend much time examining
all of your relationships etc., but I did see enough to realize
at least part of what your issue is. For now I'll limit this post
to just tblParts and tblPartRev which are related via pkPartID.
You can apply this same logic to your other procedures for
data from tables further down the relationship chain.

First, a couple of notes.

1)When using an event that has a built in Cancel event
(BeforeUpdate, BeforeInsert, etc.), if you do want to
cancel the update you set the Cancel argument to True
(not False as you currently have in your code).

2)I don't think your boolean variable – bOK – is even
necessary, so I left it out of this example. You appear to
using it to try and control what happens in the AfterUpdate
event, but if you cancel the BeforeUpdate event properly the
AfterUpdate event never fires, and focus never leaves the
control in question.

3)I may have some of your field/table names slightly wrong.

So, on your unbound form when a user enters a part number in the
sPartNumber text box, you need to see if the value exists in tblParts.
Simple enough;

Private Sub sPartNumber_BeforeUpdate (Cancel As Integer)

'Lookup the value in the table using Dlookup.
'Dlookup will return Null if the value is not found so
'we check for that using the IsNull function.

If IsNull(DLookup(“txtPartNoâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)) Then
'No match found. Cancel the update and display a message.
Cancel = True
MsgBox “The part number you entered was not found.â€
Else
SaveSetting:' blah, blah, whatever you code was for
'saving to the registry
End If

End Sub

Next, the user enters a revision value and you need to check tblPartRev
to see if the value entered exists for the part number entered previously.
However, the part number doesn't exist in tblPartRev, the part numbers
PK value does (in the fkPartID field). So you have to get that part
numbers PK value first, then use it as part of the criteria in another
Dlookup statement;

Private Sub sPartRev_BeforeUpdate (Cancel As integer)

'Declare a variable to store the PK value
Dim lngID As Long

'Get the Pk value of the part number in sPartNumber
lngID = Dlookup(“pkPartIDâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)

'Now check if the revision value entered exists for that
'part numbers PK value in tblPartRev

If IsNull(DLookup(“txtRevâ€, “tblPartRevâ€, “txtRev='†_
& Me.sPartRev & “' And fkPartID=†& lngID)) Then
'No match was found
Cancel = True
MsgBox “The revision value entered does not exist for this part number.â€
Else
'your SaveSetting code
End If

End Sub

There are other ways this could be accomplished as well, using
recordsets, etc., but the general idea would be the same. As you
move through the relationship chain from table to table, you have
to get the PK value of the previously entered value and use that
in your criteria.
--
_________

Sean Bailey

Bear with me, I will explain what I know.

A text box doesn't have "choices". A user can enter a value, or you can
display a value. You could use code to build a list of values separated
by, for example, a comma and display it in the text box, but the user
would not be able to "choose"one of the values. Is that what you are
trying to accomplish?

The DCount is the list based on the tblParts table. Any entry not mtaching
tblParts entries is redirected back to the user to re-enter the value.

I wish to accomplish:
If the value in sPartNumber (textbox1) is in tblParts (txtPartNo) then the
value entered in sPartRev (textbox2) should evaluate to tblParts and
tblPartRev (txtRev).

This is simply going to count the number of records that
meet the criteria, so I'm not sure how that's going to meet
your needs, plus you probably would need to add >0 at the
end of the statement (If DCount(blah,blah,blah)>0 Then).

I will add ">0" as suggested.

varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")

Your syntax is wrong here. The field name comes first, then the
table name, then the criteria. Plus Dlookup is only going to
return a single row (the first one it finds that meets the
criteria), not a set of records.

Hmm. I guess I need a "record set"?

I have this posted on another site, it hasn't had any activity which is why
Ihave tried this site.
http://www.access-programmers.co.uk/forums/showthread.php?t=182566
It has a small db attached in ZIP format.

All help is appreciated.

--
Regards

Rick
NT4.0
Office 2k

 

[Rick S.]

p.s.
I am (was) using bOk to reset focus back to the originating textbox when an
entered value is\was wrong. You are correct in that the focus is not changed
after an invalid entry using your code example.

One more time, Thank you!

--
Regards

Rick
Vista
Office 2k
CA Security Center (firewall etc.)

I took a look at your db. I didn't spend much time examining
all of your relationships etc., but I did see enough to realize
at least part of what your issue is. For now I'll limit this post
to just tblParts and tblPartRev which are related via pkPartID.
You can apply this same logic to your other procedures for
data from tables further down the relationship chain.

First, a couple of notes.

1)When using an event that has a built in Cancel event
(BeforeUpdate, BeforeInsert, etc.), if you do want to
cancel the update you set the Cancel argument to True
(not False as you currently have in your code).

2)I don't think your boolean variable – bOK – is even
necessary, so I left it out of this example. You appear to
using it to try and control what happens in the AfterUpdate
event, but if you cancel the BeforeUpdate event properly the
AfterUpdate event never fires, and focus never leaves the
control in question.

3)I may have some of your field/table names slightly wrong.

So, on your unbound form when a user enters a part number in the
sPartNumber text box, you need to see if the value exists in tblParts.
Simple enough;

Private Sub sPartNumber_BeforeUpdate (Cancel As Integer)

'Lookup the value in the table using Dlookup.
'Dlookup will return Null if the value is not found so
'we check for that using the IsNull function.

If IsNull(DLookup(“txtPartNoâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)) Then
'No match found. Cancel the update and display a message.
Cancel = True
MsgBox “The part number you entered was not found.â€
Else
SaveSetting:' blah, blah, whatever you code was for
'saving to the registry
End If

End Sub

Next, the user enters a revision value and you need to check tblPartRev
to see if the value entered exists for the part number entered previously.
However, the part number doesn't exist in tblPartRev, the part numbers
PK value does (in the fkPartID field). So you have to get that part
numbers PK value first, then use it as part of the criteria in another
Dlookup statement;

Private Sub sPartRev_BeforeUpdate (Cancel As integer)

'Declare a variable to store the PK value
Dim lngID As Long

'Get the Pk value of the part number in sPartNumber
lngID = Dlookup(“pkPartIDâ€, “tblPartsâ€, “txtPartNo='†_
& Me.sPartNumber & “'â€)

'Now check if the revision value entered exists for that
'part numbers PK value in tblPartRev

If IsNull(DLookup(“txtRevâ€, “tblPartRevâ€, “txtRev='†_
& Me.sPartRev & “' And fkPartID=†& lngID)) Then
'No match was found
Cancel = True
MsgBox “The revision value entered does not exist for this part number.â€
Else
'your SaveSetting code
End If

End Sub

There are other ways this could be accomplished as well, using
recordsets, etc., but the general idea would be the same. As you
move through the relationship chain from table to table, you have
to get the PK value of the previously entered value and use that
in your criteria.
--
_________

Sean Bailey

Bear with me, I will explain what I know.

A text box doesn't have "choices". A user can enter a value, or you can
display a value. You could use code to build a list of values separated
by, for example, a comma and display it in the text box, but the user
would not be able to "choose"one of the values. Is that what you are
trying to accomplish?

The DCount is the list based on the tblParts table. Any entry not mtaching
tblParts entries is redirected back to the user to re-enter the value.

I wish to accomplish:
If the value in sPartNumber (textbox1) is in tblParts (txtPartNo) then the
value entered in sPartRev (textbox2) should evaluate to tblParts and
tblPartRev (txtRev).

This is simply going to count the number of records that
meet the criteria, so I'm not sure how that's going to meet
your needs, plus you probably would need to add >0 at the
end of the statement (If DCount(blah,blah,blah)>0 Then).

I will add ">0" as suggested.

varX = DLookup("[tblParts]", "[txtPartNo]", "txtRev = '" & sPartRev & "'")

Your syntax is wrong here. The field name comes first, then the
table name, then the criteria. Plus Dlookup is only going to
return a single row (the first one it finds that meets the
criteria), not a set of records.

Hmm. I guess I need a "record set"?

I have this posted on another site, it hasn't had any activity which is why
Ihave tried this site.
http://www.access-programmers.co.uk/forums/showthread.php?t=182566
It has a small db attached in ZIP format.

All help is appreciated.

--
Regards

Rick
NT4.0
Office 2k