Can't use find or replace now.

[Richard1941]

I have a form in which users are expected to enter request data, but not all the data is known at the beginning. A button on the page takes you to another form for finding an existing request. When you select the one you want by clicking on an "edit" button, you are taken back to the first form, hopefully with the earlier data filled in. The primary key of the desired record is correctly stored in a TempVar, but when I get back to the first form, in the Open event code, I get the message "You can't use find or replace now." The line that causes the error is
DoCmd.FindRecord strRequestID, , True, , , , True

What's causing the error and what can I do about it?

Thanks,
Richard

 

[boblarson]

You can use this:

Dim rst As DAO.Recordset
Dim lngID as Long
 
Set rst = Forms!YourFirstFormNameHere.RecordsetClone
 
lngID = Me!IDFieldHereOnSelectForm
 
rst.FindFirst "[IDFieldNameInFirstForm]=" & lngID
 
If rst.NoMatch Then
   Msgbox "No match found.", vbInformation, "No match"
Else
   Forms!YourFirstFormNameHere.Bookmark = rst.Bookmark
End If

 

[Richard1941]

OK, Thanks. Now I have the correct value in lngID, but on the next line I get "The Microsoft Access Database engine does not recognize 'RequestID' as a valid field name or expression." That line is:

rst.FindFirst "[RequestID]=" & lngID

What next?

 

[boblarson]

Is RequestID an actual field name in your first form's record source and on the form?

 

[Richard1941]

It is the primary key in the record source and it is a control on the form.

 

[boblarson]

It is the primary key in the record source and it is a control on the form.

Change the name of the control to txtRequestID so Access doesn't get confused (and before you change the name I would highly suggest turning off Name Auto Correct, if you haven't already. It is in Access Options > Current Database.

 

[Richard1941]

Didn't help. Here's the complete On Open code:

Private Sub Form_Open(Cancel As Integer)
Dim strRequestID As String

Dim rst As DAO.Recordset
Dim lngID As Long

Set rst = Forms!frmServicesByMember.RecordsetClone
lngID = TempVars!RequestID
rst.FindFirst "[RequestID]=" & lngID
If rst.NoMatch Then
MsgBox "No match found.", vbInformation, "No match"
Else
Forms!frmServicesByMember.Bookmark = rst.Bookmark
End If
End Sub

Suggestions?

 

[boblarson]

Umm, this is the ON OPEN event for WHICH FORM?

And, ON LOAD is the event you want if you want values that are available (On Open is too soon).

The code I gave you was for the event where you selected a record on the selection form.

 

[Richard1941]

Form frmEnterMemberRequests goes to frmServicesByMember to find a specific request. Eventually that will return to frmEnterMemberRequests, but for now it goes to frmEnterMemberRequests2. The code I showed above is in Open for frmEnterMemberRequests2. The problem has been to get the form to load existing data instead of opening with a blank form.

I just moved that code to On Load. It no longer produces the error message, but it doesn't load any data. Does it matter that the data controls are on a tab rather than on the main form?

 

[boblarson]

1. The frmEnterMemberRequests2, does it have a query as its record source?

2. Is the form's DATA ENTRY property properly set to NO?

 

[Richard1941]

The record source is a query. DATA ENTRY is set to Yes.

 

[boblarson]

DATA ENTRY is set to Yes.

Well, there's your problem. Change it to NO. Setting it to YES means that it will NOT have ANY previous records in it. It will only have what is entered at the time the form is opened until the time it is closed or requeried.

 

[Richard1941]

Ureka! As soon as I changed it, the form filled out, even in Layout View.

Thanks so much.

 

[boblarson]

Ureka! As soon as I changed it, the form filled out, even in Layout View.

Thanks so much.

Glad we got it figured out.