Match Data

  • Thread starter Thread starter Brigham Siton
  • Start date Start date
B

Brigham Siton

I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a table that
contains the price and populate the Price Control if it finds a match.

Thank you very much.

Brigham
 
Me.FilterOn = False
Me.Filter = "[LastName] = '" & Me![cboLastName] & "'"
Me.FilterOn = True
 
Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no match is
found, we'll put the results into a variable of variant type. I'm guessing
that PartNo and Modifier are both text fields - if so, the test values have
to be wrapped in quotes so that they can be corrctly evaluated as strings
when the query gets to Jet (the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo & """" _
& " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )
 
Great. Thanks. I will give it a shot.


Sandra Daigle said:
Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no match is
found, we'll put the results into a variable of variant type. I'm guessing
that PartNo and Modifier are both text fields - if so, the test values have
to be wrapped in quotes so that they can be corrctly evaluated as strings
when the query gets to Jet (the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo & """" _
& " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brigham said:
I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a table
that contains the price and populate the Price Control if it finds a
match.

Thank you very much.

Brigham
Click to expand...
Click to expand...
 
Sandra,

I forgot to mention that the form the control is in is a subform.

How do I refer to the subform using the dlookup?

Thanks.



Sandra Daigle said:
Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no match is
found, we'll put the results into a variable of variant type. I'm guessing
that PartNo and Modifier are both text fields - if so, the test values have
to be wrapped in quotes so that they can be corrctly evaluated as strings
when the query gets to Jet (the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo & """" _
& " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brigham said:
I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a table
that contains the price and populate the Price Control if it finds a
match.

Thank you very much.

Brigham
Click to expand...
Click to expand...
 
Instead of -

me.partno

You would use:

me.MySubFormControlName.Form.Partno

Replace 'MySubFormControlName' with the name of the subform control on the
main form. Note that this is not necessarily the same as the name of the
form object that is referenced in the ControlSource of the subform control.
To be sure, open the main form and click once on the subform then check the
name property under the Other tab. Whatever you find there is what belongs
in place of MySubFormControlName.

Also replace 'Partno' with the correct name of the control on the subform.

Do the same for the Modifier control.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Brigham said:
Sandra,

I forgot to mention that the form the control is in is a subform.

How do I refer to the subform using the dlookup?

Thanks.



Sandra Daigle said:
Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no
match is found, we'll put the results into a variable of variant type.
I'm guessing that PartNo and Modifier are both text fields - if so, the
test values have to be wrapped in quotes so that they can be corrctly
evaluated as strings when the query gets to Jet (the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo & """" _
& " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brigham said:
I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a table
that contains the price and populate the Price Control if it finds a
match.

Thank you very much.

Brigham
Click to expand...
Click to expand...
Click to expand...
 
Great. I will give it a shot.

Thank you very much again. You've been a great help.

Brigham


Sandra Daigle said:
Instead of -

me.partno

You would use:

me.MySubFormControlName.Form.Partno

Replace 'MySubFormControlName' with the name of the subform control on the
main form. Note that this is not necessarily the same as the name of the
form object that is referenced in the ControlSource of the subform control.
To be sure, open the main form and click once on the subform then check the
name property under the Other tab. Whatever you find there is what belongs
in place of MySubFormControlName.

Also replace 'Partno' with the correct name of the control on the subform.

Do the same for the Modifier control.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Brigham said:
Sandra,

I forgot to mention that the form the control is in is a subform.

How do I refer to the subform using the dlookup?

Thanks.



Sandra Daigle said:
Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no
match is found, we'll put the results into a variable of variant type.
I'm guessing that PartNo and Modifier are both text fields - if so, the
test values have to be wrapped in quotes so that they can be corrctly
evaluated as strings when the query gets to Jet (the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo & """" _
& " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brigham Siton wrote:
I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a table
that contains the price and populate the Price Control if it finds a
match.

Thank you very much.

Brigham
Click to expand...
Click to expand...
Click to expand...
 
You're welcome - let me know if you still have questions. :-)

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Brigham said:
Great. I will give it a shot.

Thank you very much again. You've been a great help.

Brigham


Sandra Daigle said:
Instead of -

me.partno

You would use:

me.MySubFormControlName.Form.Partno

Replace 'MySubFormControlName' with the name of the subform control
on the main form. Note that this is not necessarily the same as the
name of the form object that is referenced in the ControlSource of
the subform control. To be sure, open the main form and click once
on the subform then check the name property under the Other tab.
Whatever you find there is what belongs in place of
MySubFormControlName.

Also replace 'Partno' with the correct name of the control on the
subform.

Do the same for the Modifier control.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Brigham said:
Sandra,

I forgot to mention that the form the control is in is a subform.

How do I refer to the subform using the dlookup?

Thanks.



Hi Brigham,

You can use Dlookup for this - since Dlookup returns a null when no
match is found, we'll put the results into a variable of variant
type. I'm guessing that PartNo and Modifier are both text fields -
if so, the test values have to be wrapped in quotes so that they
can be corrctly evaluated as strings when the query gets to Jet
(the database engine).

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=""" & me.partNo &
"""" _ & " And Modifier = """ & me.Modifier & """")

If they are not text, just drop the extra quotes.

dim varPrice as variant
varPrice=dlookup("curPrice","tblPrices","PartNo=" & me.partNo & _
& " And Modifier = " & me.Modifier )


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Brigham Siton wrote:
I am using access 97.

I have a fom that contains the following controls:

PartNo - 5-digit code
Modifier - 2 digit code (sometimes available)
Price - Price for the item

I would want to compare/match the PartNo & Modifier combo on a
table that contains the price and populate the Price Control if
it finds a match.

Thank you very much.

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