not in list

R

Russ

I have the follwing code:
Private Sub TypeofMedication_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!TypeOfMedication
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
Set db = CurrentDb
Set RS = db.OpenRecordset("lookupEntry", dbOpenDynaset)
On Error Resume Next
RS.AddNew
RS!Form = NewData
RS.Update

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

End If

Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

How do I alter this so that the value i type goes into a table called
"lookupEntry" into a field called "LookupDisplay". i also need to set the
value of a field in teh table called "LookupID" to RxMeds.
 
M

Michel Walsh

Hi,



DoCmd.RunSQL.Execute "INSERT INTO tableName(fieldName)
VALUES(FORMS!Formsname!ControlName)"


would insert the value from the specified control into the specified field
and table.


As well as updating a record, you need a clause WHERE to specify which
record (a table have many records), and you can use an SQL statement such
like:


UPDATE tablename SET fieldName = newValue WHERE someKeyField = someValue



Hoping it may help,
Vanderghast, Access MVP
 
Top