Problem with find first

[Dennis]

I’m on Access 2003 on XP Office Pro on XP Pro w SP3.

I have trying to read a record using FindFirst and it is not working.

My table is called tblWarranty. Its primary key is ItemNo. It is an
automatically generated number. I have a query called qrytblWarranty that
joins the tblWarranty to tblCustomer (left join – all rcd in tblWarranty &
only those matching in tblCustomer). There is no selection criteria in the
query.

I have a form which access the warranty data using qrytblWarranty. There is
no filter on the form. The selects the customer from a drop down combo box.
The drop down combo box for Warranty parts limits the data that appears in
the drop down box to the customer’s parts. All of this works great.

When the user selects a part from the Repaired Parts list, I want to go
directly to that record. However, the FindFirst fails every time. Here is my
code.

lngItemNo = Nz(Me.cboItemNo, 0)
strFindFirst = "((ItemNo) = “ & lngItemNo & “)"
Set rs = Me.Recordset.Clone
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark
End If

I copied the above code from a program that works. I’ve read the other
FindFirst questions in this forum. I tried some of those suggestions, but
nothing worked.

How do I know that the record set I cloned really has the data I think it
has? Is there a way to view the data that is in the record set?

Thanks for any help.

 

[Dennis]

AccessVandal,

When I said I copied it from another program, what I meant to imply was I
copied working code from another program I wrote.

Before posting, I've already tried the following code (and it failed also):

Set rs = Me.RecordsetClone
rs.FindFirst "ItemNo = 3"
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark
End If

That is why I suspect that maybe my record set is not looking at what I
think it is looking at. My recordset at I understand it is defined in the
Form's RecordSet, which is qrytblWarranty - without any filter or
restrictions. Am wrong on that point?

Is there a way to put a loop in the program and read through the recordset
and display the keys as I go? I would like to know if I really looking at
what I think I'm looking at.


Dennis

 

[Dirk Goldgar]

I’m on Access 2003 on XP Office Pro on XP Pro w SP3.

I have trying to read a record using FindFirst and it is not working.

My table is called tblWarranty. Its primary key is ItemNo. It is an
automatically generated number. I have a query called qrytblWarranty that
joins the tblWarranty to tblCustomer (left join – all rcd in tblWarranty &
only those matching in tblCustomer). There is no selection criteria in
the
query.

I have a form which access the warranty data using qrytblWarranty. There
is
no filter on the form. The selects the customer from a drop down combo
box.
The drop down combo box for Warranty parts limits the data that appears in
the drop down box to the customer’s parts. All of this works great.

When the user selects a part from the Repaired Parts list, I want to go
directly to that record. However, the FindFirst fails every time. Here is
my
code.

lngItemNo = Nz(Me.cboItemNo, 0)
strFindFirst = "((ItemNo) = “ & lngItemNo & “)"
Set rs = Me.Recordset.Clone
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark
End If

I copied the above code from a program that works. I’ve read the other
FindFirst questions in this forum. I tried some of those suggestions, but
nothing worked.

I would do it like this:

With Me.RecordsetClone
.FindFirst "ItemNo = " & Nz(Me.cboItemNo, 0)
If .NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = .Bookmark
End If
End With

If that doesn't work right, check the Bound Column property of the combo
box, to make sure it's set to the ItemNo field. You might also check the
actual field names in the form's recordsource query by opening the query up
in datasheet view. Or you could add this code just for debug purposes:

Dim fld As Access.Field
Debug.Print Me.cboItemNo
With Me.RecordsetClone
For Each fld In .Fields
Debug.Print fld.Name, fld.Value
Next fld
End With

Look in the Immediate window for the output.

How do I know that the record set I cloned really has the data I think it
has? Is there a way to view the data that is in the record set?

Normally, that's what the form is for.

 

[Dirk Goldgar]

Oops, typos check

Set rs = Me.RecordsetClone

No period after recordset.

I would use RecordsetClone, too, but Me.Recordset.Clone is valid.

 

[Dennis]

Dirk,

When I paste the following code in:

Dim fld As Access.Field

Debug.Print Me.cboItemNo
With Me.RecordsetClone
For Each fld In .Fields
Debug.Print fld.Name, fld.Value
Next fld
End With


I receive the following error on the Dim Statement:

User-defined type not defined.

Also, more information.

The ItemNo field is bound column is 1, which is the column of the ItemNo
value from the Row Source Query.

I was able to do a ?Me.Recordsource in the immediate box and it display
qrysfWarranty, which is the table I'm supposed to be using.

As a test, I explicity open the record set and read the record I want and it
finds the record. However, when I use the same strFindFirst and the
ME.RECORDSETCLONE method, I can not read the record. Since I'm using the
same strFindFirst for both reads, and one works but the other does not, does
that tell me that the ME.RECORDSETCLONE must be filtered or otherwise
restricted. I've search my code for filters and selection criteria, but I
can not find any.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim lngItemNo As Long
Dim strFindFirst As String

lngItemNo = Nz(Me.cboItemNo, 0)
strFindFirst = "([ItemNo] = " & lngItemNo & ")"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrysfWarranty")
rst.FindFirst strFindFirst ' works
great
If rst.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rst.Bookmark
End If


Set rs = Me.RecordsetClone
rs.FindFirst strFindFirst ' fails
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark
End If


I'm am cutting all of the code out of the program and adding in piece by
piece until I can find where the program is filtering the data.

Also, is there anyway to list the data from within the program. That is,
can I sequential read in all of the keys from ME.RECORDSETCLONE and display
them using debug.print? This way I will know what the program is really
seeing.

Thanks for you help.

 

[Dirk Goldgar]

Dirk,

When I paste the following code in:

Dim fld As Access.Field

Debug.Print Me.cboItemNo
With Me.RecordsetClone
For Each fld In .Fields
Debug.Print fld.Name, fld.Value
Next fld
End With


I receive the following error on the Dim Statement:

User-defined type not defined.

Oops! Sorry, that should have been

Dim fld As DAO.Field

Also, more information.

The ItemNo field is bound column is 1, which is the column of the ItemNo
value from the Row Source Query.

I was able to do a ?Me.Recordsource in the immediate box and it display
qrysfWarranty, which is the table I'm supposed to be using.

As a test, I explicity open the record set and read the record I want and
it
finds the record. However, when I use the same strFindFirst and the
ME.RECORDSETCLONE method, I can not read the record. Since I'm using the
same strFindFirst for both reads, and one works but the other does not,
does
that tell me that the ME.RECORDSETCLONE must be filtered or otherwise
restricted. I've search my code for filters and selection criteria, but I
can not find any.

Is your form's DataEntry property set to Yes, by any chance? If so, it
should certainly be No.

Also, is there anyway to list the data from within the program. That is,
can I sequential read in all of the keys from ME.RECORDSETCLONE and
display
them using debug.print? This way I will know what the program is really
seeing.

Sure, like this:

Dim rs As DAO.Recordset
Dim fld As DAO.Field

With Me.RecordsetClone

If .RecordCount = 0 Then
Debug.Print "The form's recordset is empty!"
Else
.MoveFirst
Do Until .EOF
Debug.Print "*** Record Number " & .AbsolutePosition + 1 & "
***"
For Each fld In .Fields
Debug.Print fld.Name, fld.Value
Next fld
.MoveNext
Loop
End If

 

[Dennis]

Dirk,

Books are not always a wonderful thing. When I start up the program, I
want a blank screen. The book said to do this, set the Data Entry to yes.
It did not tell me that I could not read data from the table and display it.

I set the Data Entry to No. That was my problem.

However, this fixed my read issue, but now how do I have the program come up
with a blank screen?

When the program starts, it automatically firs the Form_Current which loads
the first record in the table. I don't want this to happen. I guess I could
set the data to null so nothing shows on the screen, but I have a feeling
there is a better way to accomplish this.

Thanks for you help.

 

[Dirk Goldgar]

Dirk,

Books are not always a wonderful thing. When I start up the program, I
want a blank screen. The book said to do this, set the Data Entry to yes.
It did not tell me that I could not read data from the table and display
it.

I set the Data Entry to No. That was my problem.

Data Entry mode is intended for data entry only -- not reviewing existing
records. So it opens the form with an empty recordset.

However, this fixed my read issue, but now how do I have the program come
up
with a blank screen?

When the program starts, it automatically firs the Form_Current which
loads
the first record in the table.

Actually, you have that backward. The form automatically loads the first
record, and that fires the form's Current event.

I don't want this to happen. I guess I could
set the data to null so nothing shows on the screen, but I have a feeling
there is a better way to accomplish this.

What you want, as I understand it, is to open the form with all records
present and available, but be positioned at a blank new record. If that's
so, and you always want the form to open this way, you can can use code in
the put form's Load event to move it to the new record:

'------ start of code ------
Private Sub Form_Load()

Me.Recordset.AddNew

End Sub
'------ end of code ------

That should work in every version of Access from 2000 on.

 

[Dennis]

Dirk,

Thanks for your help. It solved my problem.