SaveRecord or NewRec

[sambo]

I have a form that either allows the user to add a new serial number (and subsequent data) or update a serial number (and data) that already exist.

After the button click I want to do this:

If RecordExists in tblUnits Then 'update old data
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Else 'record doesn't exist, so add it
DoCmd.GoToRecord , , acNewRec

End If


Is this possible?

How do I check to see if that particular serial number is already in the table, tblUnits?

Help..

 

[IfButOnly]

Sambo, I havn't tested this but you should do something along this line:-

Private Sub button_Click()
On Error GoTo Err_button_Click

DoCmd.OpenForm "tblTable", acNormal, , "[Id]=" & Me!SerialNo, acFormEdit, acWindowNormal

Exit_button_Click:
Exit Sub

Err_button_Click:
DoCmd.GoToRecord , , acNewRec
Resume Exit_button_Click:

End Sub


Good luck.

 

[sambo]

I'm not sure I understand.
Why would I use the openform function when I'm dealing w/ the data in a table. The user enters data with the form, but that data is subsequently put into a table (tblUnits).
This examples looks like it opens up a form???
I'm still stuck. Could you explain.

 

[George Too]

Hi Sambo,

The following code just shows a way to get Access to look for a particular record and then prompt the user to take action:

'------------------------------------------------------------------
'See if saving ticket number already exist
Dim dbs As Database, rst As Recordset
Dim fld As Field, strInput As String

' Return reference to current database.
Set dbs = CurrentDb

' Create table-type Recordset object.
Set rst = dbs.OpenRecordset("tblMain", dbOpenTable)

' Set current index.
rst.Index = "Ticket"

' Locate record.
rst.Seek "=", Ticket

If Not rst.NoMatch Then
'Message when ticket is a match
Msg = "This ticket number has already been used.@Please use another number.@"
BoxTitle = "Ticket Number Used Warning"
DlgStyle = vbOKOnly
wId = MsgBox(Msg, DlgStyle, BoxTitle)

'Setting the Ticket value to Null and giving it the focus
Me!Ticket = Null
Me!Ticket.SetFocus

'Blanc database
Set dbsAccess = Nothing

'Closing the database
Set dbsAccess = DBEngine.Workspaces(0).Databases(0)
Set rstAccess = dbsAccess.OpenRecordset("tblMain", dbOpenDynaset)

rstAccess.Close
dbsAccess.Close
Exit Sub

End If
'-----------------------------------------------------------

Now, it is a bit messy but works great for me. One more thing, this is just a section of the code, and would need some work for it to be functional in your database (like change name of tables, controls, etc.); it does not have error handling, or code to update the records, but at least answers your original question —"How do I check to see if that particular serial number is already in the table, tblUnits?"

I am not a pro, so there might be a better way to do this. Like I said, this works like a charm for me.

 

[sambo]

You rule, dude. Works like a charm. You're right, it might not be pretty, but it works great.
Thanks

 

[Stafford]

Peter,

Jon K is helping me with a similiar problem in this post

Code in a form that finds a record

I tried to modify the code that you gave Sambo to use, but can't get that to work either.

I am SUPER frustrated with Access, VBA and databases in general.

Any help would be more than appreciated.