Form doesn't show new record (1 Viewer)

[ClaraBarton]

The following code gets a new Contact ID, and preloads a new record but the form does not move to the new record.

Public Sub AddRecord()
    On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim strLinkCriteria
Set db = CurrentDb()

strLinkCriteria = "[ContactsID]=" & NewContactID()

'reposition form to new record
Set rst = Me.RecordsetClone
If Me.RecordsetClone.RecordCount > 0 Then
    rst.FindFirst strLinkCriteria
       If Not rst.EOF Then
            Me.Bookmark = rst.Bookmark
        End If
End If
 
rst.Close
Set rst = Nothing

    Me.FirstName.SetFocus

Where I am I going wrong?

 

[tvanstiphout]

Check the help file. You need to test with NoMatch rather than EOF.

 

[The_Doc_Man]

You are working too hard. Not to mention that it is probably not a good idea to use the .RecordSetClone as an arbitrary recordset.

If you are on a form, you can do a DoCmd.GotoRecord for which one of the options is to name the form in question and a second option is acNewRecord. So get your new ID and then go to a new record to fill it in. Look in the link below for the "Parameters" section, and there is an example of using it in the link too.

DoCmd.GoToRecord method (Access)

Office VBA reference topic

learn.microsoft.com

 

[ClaraBarton]

Ahh... you make it seem so easy

Dim NewRecord
NewRecord = "ContactID = " & NewContactID()
DoCmd.GotoRecord acDataForm, "frmDetail", NewRecord

This returns a type mismatch. I don't think I want acNewRecord because it's already created. But I can't seem to get to my record.

 

[Mike Krailo]

NewRecord is the property of the form. You should not be dimensioning NewRecord because then it just becomes a variable.

   DoCmd.GoToRecord acDataForm, "frmDetail", acNewRec
   DoCmd.GoToRecord acDataForm, "frmDetail", acPrevious

 

[The_Doc_Man]

Mike beat me to it. In the code you presented in post #4, NewRecord is a declared variable that becomes a string when you store a string in it. But in the place where you put it, that is a numeric argument that won't take a string.

OK, let's clarify, ClaraBarton. Is it your goal to EITHER find an existing record with a given ID ... OR to create a new record because no one has the specified ID?

To do the search for that extant record, you WOULD use the .RecordSetClone and a .FindFirst, and you WOULD use that .Bookmark method to reposition the form. However, if .FindFirst fails to find the required record (rst.NoMatch=True), THEN you would use the DoCmd.GoToRecord method with acDataForm, Me.Name, acNewRec as the THREE arguments - because the fourth argument is only used for explicit targets and acNewRec is an implicit target.

 

[ClaraBarton]

oh. I want to find an existing record that I just created with NewContactID.

 

[Mike Krailo]

If you just created the record, it's at the bottom. That's why I went to the NewRecord first and backed up one record. That should put you on your newly created record. It does for me anyways.

 

[MarkK]

How exactly does your call to NewContactID() work? A recordset does not automatically add or remove rows based on source table changes made by some other process. If ProcessA adds a row to a table without using RecordsetA, then the new row does not exist in RecordsetA, even though it does exist in the table. In this case you must Requery or reopen the recordset.

 

[Pat Hartman]

It is poor practice to create incomplete records. If what you are trying to do is to copy a record so you can change it to make a new quote for example, there are methods we can suggest.

 

[ClaraBarton]

I Got It!

20    strLinkCriteria = "ContactsID = " & NewContactID
30    Me.Requery
40    Set rst = Me.RecordsetClone
50        rst.FindFirst strLinkCriteria
60        If Not rst.EOF Then
70            Me.Bookmark = rst.Bookmark
80        End If
90        Me.FirstName.SetFocus

Part of the problem was that I wasn't requerying the recordset, so not picking up the new record.
I know you hate my preloaded records but it's what I need to set a different number. I have safeguards to remove the record if it isn't changed.
Thank you all so much!

 

[Pat Hartman]

I know you hate my preloaded records but it's what I need to set a different number.

I don't hate anything. I just don't do things that are incorrect logically. It is poor practice to have records in your table that are invalid. PERIOD. Even if you think you can get rid of them at some point, they are there for some period of time and therefore, they will get sucked up into queries and used.

 

[MarkK]

If you are using a DAO.Recordset, as you seem to be, you need to pay heed to Tom's advice in Post #2. You need to check Recordset.NoMatch after you run Recordset.FindFirst. Consider this code...

Public Sub AddRecord()
    Dim newID as Long
    
    newID = NewContactID
    Me.Requery
    Me.GoToID newID
End Sub

Public Sub GoToID(ID as Long)
    With Me.RecordsetClone
        .FindFirst "ID = " & ID
        If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With
End Sub

And it is always handy to expose GoToID as its own method, so other consumers can use it.