Rich_Lovina
Registered User.
- Local time
- Tomorrow, 07:29
- Joined
- Feb 27, 2002
- Messages
- 225
Updating related table....more than one field
Hi there, I have the following code:
Public Function AddToSource(tbl As String, fld2update As String, NewData As String) As Integer
'Adds record to the list if not already present
'This only works if there is just one field to update....
Dim StrMessage As String
Dim dbs As Database
Dim RstTypes As Recordset
Dim Response As Integer
StrMessage = "'" & NewData & "' is not in current list" & _
" To add the item for future reference choose yes, or choose no to select from the present options."
Response = MsgBox(StrMessage, vbYesNo, "Not in List")
If Response = vbYes Then
Set dbs = CurrentDb()
Set RstTypes = dbs.OpenRecordset(tbl)
RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update
AddToSource = acDataErrAdded
dbs.Close
Else
Add2Source = acDataErrDisplay
End If
StrMessage = ""
Set dbs = Nothing
Set RstTypes = Nothing
End Function
with an event in the NotinList properties.
This code will only update one field...
Is there any code to update a second field in the related table (a yes/no field) depending on the data entered?
Example:
Fld Deptdescription has "IT acquisition" in the related table, and related table has "MM" in JobFunction field, previously added manually by user.
User enters "IT data programming", above function is triggered and this text is added to list. Can a second function see that past records having "* data *" in their string have code "DM" in JobFunction field?
Thanks any help on this one.
Hi there, I have the following code:
Public Function AddToSource(tbl As String, fld2update As String, NewData As String) As Integer
'Adds record to the list if not already present
'This only works if there is just one field to update....
Dim StrMessage As String
Dim dbs As Database
Dim RstTypes As Recordset
Dim Response As Integer
StrMessage = "'" & NewData & "' is not in current list" & _
" To add the item for future reference choose yes, or choose no to select from the present options."
Response = MsgBox(StrMessage, vbYesNo, "Not in List")
If Response = vbYes Then
Set dbs = CurrentDb()
Set RstTypes = dbs.OpenRecordset(tbl)
RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update
AddToSource = acDataErrAdded
dbs.Close
Else
Add2Source = acDataErrDisplay
End If
StrMessage = ""
Set dbs = Nothing
Set RstTypes = Nothing
End Function
with an event in the NotinList properties.
This code will only update one field...
Is there any code to update a second field in the related table (a yes/no field) depending on the data entered?
Example:
Fld Deptdescription has "IT acquisition" in the related table, and related table has "MM" in JobFunction field, previously added manually by user.
User enters "IT data programming", above function is triggered and this text is added to list. Can a second function see that past records having "* data *" in their string have code "DM" in JobFunction field?
Thanks any help on this one.