FindRecord

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

This is probably really simple, but ....

We use Access 2000.

I have three tables. Client, Matter and Records. They
are related by:
Client to Matter by client#
Matter to Records by Client.Matter#

I have the client table on a form with the matter and
records in subforms.

I need to create a text box where the user can type in a
barcode# (which is contained in the Records table) and
have the correct client, matter and barcode records
appear in the form. I can't do a simple search like I do
for the client# where I change the focus to the client#
field and bring up the Find dialog box because that
search would only search through the current client and
matter and I need it to search through ALL records.

I am converting this to Access from a Paradox 3.1 app.
There we ran a query of the 'records' table to locate the
record with the barcode#. We then got the client.matter
number from the record and from that extracted the client
number. We used a UIObject (paradox object) to attach to
the field on the form to get to the right matter and
client record.

In Access, which I'm not as familiar with, I have tried
several things but am just not sure about the right way
to do this.

Any ideas would be appreciated!
Mary
 
Mary said:
This is probably really simple, but ....

We use Access 2000.

I have three tables. Client, Matter and Records. They
are related by:
Client to Matter by client#
Matter to Records by Client.Matter#

I have the client table on a form with the matter and
records in subforms.

I need to create a text box where the user can type in a
barcode# (which is contained in the Records table) and
have the correct client, matter and barcode records
appear in the form. I can't do a simple search like I do
for the client# where I change the focus to the client#
field and bring up the Find dialog box because that
search would only search through the current client and
matter and I need it to search through ALL records.

I am converting this to Access from a Paradox 3.1 app.
There we ran a query of the 'records' table to locate the
record with the barcode#. We then got the client.matter
number from the record and from that extracted the client
number. We used a UIObject (paradox object) to attach to
the field on the form to get to the right matter and
client record.

In Access, which I'm not as familiar with, I have tried
several things but am just not sure about the right way
to do this.
Click to expand...


Do it pretty much the same way. You can use the DLookup
function to find the Matter# in Records that matches the
barcode#

varMatter = DLookup("[Matter#]", "Records", "[barcode#]=" &
txtbarcode)
varClientID = DLookup("[client#]", "Clients", "[client#]=" &
varMatter)

Now you can reposition the mainform to the desired client:

With Me.RecordsetClone
.FindFirst "[client#]=" & varClientID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
 
Thanks for your reply. I tried what you suggested and I
think it will work but I am getting the following error:

Runtime error 3070. The microsoft jet database engine
does not recognize 'varClient' as a valid field name or
expression.


This is my code:

Private Sub txtBarCodeNo_LostFocus()
Dim varClMat As Variant
Dim varOnlyClient As Variant
Dim varClient As Variant
Dim BarCodeNo As Variant

BarCodeNo = txtBarCodeNo.Text

varClMat = DLookup
("[ClientMatterNo]", "dbo_Records", "[BarCodeNo]=
BarCodeNo")

varOnlyClient = Mid(varClMat, 1, 6)

varClient = DLookup("[clnum]", "dbo_client", [clnum] =
varOnlyClient)

With Me.RecordsetClone
.FindFirst "[clnum]= varClient" -- it errors out here
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

I was reading something about Bookmarks and how they
don't always work if non-Access tables are being used. I
am using SQL tables. If that is the problem, is there
something else I can use. Or is there another problem.

Thanks alot for your help!!

Mary
-----Original Message-----
Mary said:
This is probably really simple, but ....

We use Access 2000.

I have three tables. Client, Matter and Records. They
are related by:
Client to Matter by client#
Matter to Records by Client.Matter#

I have the client table on a form with the matter and
records in subforms.

I need to create a text box where the user can type in a
barcode# (which is contained in the Records table) and
have the correct client, matter and barcode records
appear in the form. I can't do a simple search like I do
for the client# where I change the focus to the client#
field and bring up the Find dialog box because that
search would only search through the current client and
matter and I need it to search through ALL records.

I am converting this to Access from a Paradox 3.1 app.
There we ran a query of the 'records' table to locate the
record with the barcode#. We then got the client.matter
number from the record and from that extracted the client
number. We used a UIObject (paradox object) to attach to
the field on the form to get to the right matter and
client record.

In Access, which I'm not as familiar with, I have tried
several things but am just not sure about the right way
to do this.
Click to expand...


Do it pretty much the same way. You can use the DLookup
function to find the Matter# in Records that matches the
barcode#

varMatter = DLookup("[Matter#]", "Records", "[barcode#] =" &
txtbarcode)
varClientID = DLookup("[client#]", "Clients", "[client#] =" &
varMatter)

Now you can reposition the mainform to the desired client:

With Me.RecordsetClone
.FindFirst "[client#]=" & varClientID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Click to expand...
 
You can not use the name of a VBA variable in the where part
of the DLookup, you have use the value of the variable as I
tried to demonstrate in my initial reply:

DLookup("ClientMatterNo","dbo_Records","BarCodeNo=" &
BarCodeNo)

DLookup("clnum","dbo_client","clnum=" & varOnlyClient)

but it doesn't make sense to look up the value of clnum
where clnum is equal to varOnlyClient, since it will only
find the value varOnlyClient, which you've already
calculated.

You have the same problem on the FindFirst:

.FindFirst "[clnum]= " & varClient

The above syntax is appropriate for numeric values only. If
any of those fields are text type, then the values have to
be enclose in quotes:

.FindFirst "[clnum]= """ & varClient & """"
--
Marsh
MVP [MS Access]



Thanks for your reply. I tried what you suggested and I
think it will work but I am getting the following error:

Runtime error 3070. The microsoft jet database engine
does not recognize 'varClient' as a valid field name or
expression.


This is my code:

Private Sub txtBarCodeNo_LostFocus()
Dim varClMat As Variant
Dim varOnlyClient As Variant
Dim varClient As Variant
Dim BarCodeNo As Variant

BarCodeNo = txtBarCodeNo.Text

varClMat = DLookup
("[ClientMatterNo]", "dbo_Records", "[BarCodeNo]=
BarCodeNo")

varOnlyClient = Mid(varClMat, 1, 6)

varClient = DLookup("[clnum]", "dbo_client", [clnum] =
varOnlyClient)

With Me.RecordsetClone
.FindFirst "[clnum]= varClient" -- it errors out here
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

I was reading something about Bookmarks and how they
don't always work if non-Access tables are being used. I
am using SQL tables. If that is the problem, is there
something else I can use. Or is there another problem.

-----Original Message-----
Mary said:
This is probably really simple, but ....

We use Access 2000.

I have three tables. Client, Matter and Records. They
are related by:
Client to Matter by client#
Matter to Records by Client.Matter#

I have the client table on a form with the matter and
records in subforms.

I need to create a text box where the user can type in a
barcode# (which is contained in the Records table) and
have the correct client, matter and barcode records
appear in the form. I can't do a simple search like I do
for the client# where I change the focus to the client#
field and bring up the Find dialog box because that
search would only search through the current client and
matter and I need it to search through ALL records.

I am converting this to Access from a Paradox 3.1 app.
There we ran a query of the 'records' table to locate the
record with the barcode#. We then got the client.matter
number from the record and from that extracted the client
number. We used a UIObject (paradox object) to attach to
the field on the form to get to the right matter and
client record.

In Access, which I'm not as familiar with, I have tried
several things but am just not sure about the right way
to do this.
Click to expand...


Do it pretty much the same way. You can use the DLookup
function to find the Matter# in Records that matches the
barcode#

varMatter = DLookup("[Matter#]", "Records", "[barcode#] =" &
txtbarcode)
varClientID = DLookup("[client#]", "Clients", "[client#] =" &
varMatter)

Now you can reposition the mainform to the desired client:

With Me.RecordsetClone
.FindFirst "[client#]=" & varClientID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Click to expand...
Click to expand...
 
Back
Top