Adding an item to a table from a combo box

[prana1]

Hi all,
Thanks for all you support so far. I am coming a long way with this
program. OK, still kind of new, but I have created a combo box that reads a
table with vendor names and vendor numbers, used for SAP. When a user types
in a new vendor, I would like it to
1) prompt the user that it is correct,
2) allow them to verify the spelling is correct and make changes if necessary
3) prompt them to "add vendor number to list?"
4) check to see if there is a vendor with that name in SAP - can this be
done? I think this is the hardest part
5) write back that name and vendor to the vendor_names table, where vendor
names and vendor numbers is stored.

anyone have any ideas on this one? Please keep in mind I am fairly new,
and still taking a class that, of which I am about a 1/4 of the way through.

Thanks,

Eric

 

[Scottgem (MVP)]

Use the NotInList event of the combo. Check Access Help for examples.

If you can ODBC into Your SAP table, then its possible to check it.

 

[prana1]

Thanks Scott. I set up an event procedure (I think that’s what it is) from
finding code from a web search (below).

With this, I was able to allow the form to accept a new vendor name, and
prompt the user if it is to be added to the list. If so, it adds the name to
the table. Simple enough.

What I’d like it do also is ask for is the vendor number, which is what I
need to have as well.
So, prompt if the user if he/she has the vendor #, write both to my "vendor"
table, which has the vendor name and vendor#.

Maybe it would go something like this: "Do you have the vendor #?"
On yes prompt for it, write both to my table. (don't know how to do this part)

And on no: "Please check SAP for the vendor # using XYZ command.
Enter it here (___text box offered___). “

“If you do not find it in SAP, request one from the following site:
"(hyperlink to internal site to add new vendor)".

If no vendor number, and user went to request one, display "Please put this
invoice aside. Enter it when you get the new vendor # in your email"

And then go on to the next record.

OK, I am no VB guru, but this is what I have so far from a website that I
modifed:

Private Sub Vendor_NotInList(NewData As String, Response As Integer)
On Error GoTo cboJobTitle_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The vendor " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Vendor Not On List")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Vendors([Vendor]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new vendor has been added to the list." _
, vbInformation, "New Vendor Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a vendor from the list." _
, vbInformation, "Please choose Vendor"
Response = acDataErrContinue
End If
cboJobTitle_NotInList_Exit:
Exit Sub
cboJobTitle_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboJobTitle_NotInList_Exit
End Sub