not in list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top