How to Find Record if Exists, Add if not?

[Guest]

How to Find Record if Exists, Add if not?

I have a form where user will enter the vendor ID, which is a unique ID.
They enter it off a long list of information listing many vendors and it's
hard to know if the vendorID already exists in the database, until they tab
through all the fields and get the duplicate record error. Then they press
Esc and do a search on that vendorID to pull up the existing record to
update.

VendorID format is similar to this A123456789ST (Usees a prefix, Vendor
FEIN, State abbrev.)

I want to automate this. when they enter the VendorID, I want it to search
if it exists, go to that record if it does exits. If it does not exist, I
want it to add the new record.

How can I do this?

 

[George Nicholson]

If Nz(DLookup("VendorID", "VendorTable", "VendorID = '" & Me.txtVendorID &
"'"),"") = "" Then
'VendorID does not exist in VendorTable
Else
'VendorID does exist
End If

 

[Guest]

You could use a combo to find the vendor id.

Change SomeCombo - to whatever you call it
Change SomeOtherField- to the 1st field you want to go to
Change TableName - to whatever it is

As you can see I have added a bit to allow users to just type in the word
"New" into the combo which will create a new record if needed. - Or you can
delete this bit if you want

Aircode so you will need to check it but it should be OK ??


Private Sub SomeCombo_AfterUpdate()
If Me.SomeCombo = "New" Then
DoCmd.GoToRecord , , acNewRec
SomeOtherField.SetFocus
Else
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[VendorID] = " & Str(Me![SomeCombo])
Me.Bookmark = rs.Bookmark
End If
Me.SomeCombo = ""
Me.SomeOtherField.SetFocus
End Sub

Private Sub SomeCombo_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'" & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add New Record?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Have another go?"
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TableName", dbOpenDynaset)
rs.AddNew
rs![VendorName] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Hope this helps

 

[John W. Vinson]

How to Find Record if Exists, Add if not?

I have a form where user will enter the vendor ID, which is a unique ID.
They enter it off a long list of information listing many vendors and it's
hard to know if the vendorID already exists in the database, until they tab
through all the fields and get the duplicate record error. Then they press
Esc and do a search on that vendorID to pull up the existing record to
update.

VendorID format is similar to this A123456789ST (Usees a prefix, Vendor
FEIN, State abbrev.)

I want to automate this. when they enter the VendorID, I want it to search
if it exists, go to that record if it does exits. If it does not exist, I
want it to add the new record.

How can I do this?

Ummmm.... why make them enter a big complex ID and *then* check?

Give them a Combo Box on the form selecting the existing IDs. They can start
typing it and it will autocomplete to find the ID if it's there. Use the
combo's Not In List event (search for "Not In List" at
http://www.mvps.org/access for sample code) to jump to the new record if there
is no such vendor.

John W. Vinson [MVP]

 

[Guest]

Hello,
Thanks to all 3 of you. I'm sorry I didn't resond sooner but I am just now
getting a chance to work on this again. I hope you will still see my reply.
I did decide to go with the combo box and am trying to add the code to the
Not In List Event.

I copied the code directly and pasted it in the Not in list code module
directly, then changed the names. I'm recieving an error when it runs:
"Compile error: User-defined type not defined. "
The line that is highlighted with this error is:
"Dim Db As DAO.Database"

The full event code is pasted below:
Private Sub cboVendorNumber_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available VendorNumber " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Number to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblVendors", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!VendorNumber = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

End Sub

 

[George Nicholson]

I'm recieving an error when it runs:

"Compile error: User-defined type not defined. "
The line that is highlighted with this error is:
"Dim Db As DAO.Database"

for Access 2000, 2002/XP and 2003:
In the VB editor: Tools>References: Make sure you have a check by "Microsoft
DAO 3.x Object Library".

(Access 97 was DAO 2.6 and I think DAO is included in the Access library
itself for 2007)

 

[Douglas J. Steele]

Access 97 was DAO 3.5. Access 2000, 2002 and 2003 (and MDBs in Access 2007)
use DAO 3.6.

 

[Guest]

Thanks! That worked perfectly.

Now I'm having another problem.
When it adds the new record, it doesn't update on the form.
When I say that I mean, it seems to add the new record but then doesn't go
to the new record. I have to close the form and open it again, then go to
that record to see it.
I tried adding me.refresh in various places but that didn't work and got me
stuck in a loop of sorts where it kept popping up the msgbox.

Any ideas?