Search Records, if record not found, create new

G

Guest

I have the following code in place to search my table for a record matching
the text entered into an unbound textbox:

Private Sub Text14_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.Text14) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Part ID] = """ & Me.Text14 & """"
If rs.NoMatch Then
DoCmd.S
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

I would like to change the following portion of the code so that if the
record is not found, it automatically copies the text in the unbound textbox
and pastes it into the "Part ID" field of my form creating a new record in my
table:

If rs.NoMatch Then
DoCmd.S
MsgBox "Not found: filtered?"

Any help would be greatly appreciated.
 
G

Guest

maybe something like:

If rs.NoMatch Then
me.unboundfield= me.text14
end if

Maurice
 
S

strive4peace

Hello,

Is PartID a text value?

if not me.newrecord then
DoCmd.RunCommand acCmdRecordsGoToNew
me.[Part ID] = me.text14
end if

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
G

Guest

Thank you very much Crystal!

Also, do you know how to clear that unbound textbox once the new record is
saved?

strive4peace said:
Hello,

Is PartID a text value?

if not me.newrecord then
DoCmd.RunCommand acCmdRecordsGoToNew
me.[Part ID] = me.text14
end if

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have the following code in place to search my table for a record matching
the text entered into an unbound textbox:

Private Sub Text14_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.Text14) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Part ID] = """ & Me.Text14 & """"
If rs.NoMatch Then
DoCmd.S
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

I would like to change the following portion of the code so that if the
record is not found, it automatically copies the text in the unbound textbox
and pastes it into the "Part ID" field of my form creating a new record in my
table:

If rs.NoMatch Then
DoCmd.S
MsgBox "Not found: filtered?"

Any help would be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top