Updating Related Table (1 Viewer)

[Rich_Lovina]

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.

 

[billyr]

I take it you can call this function from the NotInList event from different cbos with different data sources and that your use of "related table" refers to the calling cbo's data source.
Not a bad idea - unless - you want it to perform operations unique to a particular table and/or particular data. In which case, a general purpose function is not appropiate. The function is not that big. Why not duplicate it for those cbos that require special processing and add the code for those situations. I like to display an appropiate form where it is easy to assure all required data is entered for the record in question.
By the way, looking at your code, it seems nobody ever clicked "No". ie "Add2Source = acDataErrDisplay"

 

[Rich_Lovina]

Thanks your input. I originally found this sourcecode in the forum, then with help from member 'Fornation', built a list of key text words which is run separately but could be button initiated, so your input helps clarify the way to go. And yes the option 'no' never selected.

Real problem is 7,000 unique text strings and c.400 keywords, so the manual work is still there somewhat.

 

[billyr]

Regarding "...past records having "* data *" in their string have code "DM" in JobFunction field?", if this is a correction or update to existing data, why not use a stored update query to make the change. Granted, for large tables it may take a while to run; but, for a one time pass it still represents less time than manually updating. Then all you have to code is the mechanism to assure future data is entered like you want it. Another idea is make a form with a cmd button and a batch job behind the button to "fix" existing data. Then, delete the form.

 

[Rich_Lovina]

Thanks Bill, I did more or less that, and found my list of special keyword text, and all the bits and pieces, including 24 updating queries, macroed them, wacked em all together and put a cmd button on the users entry form.
Of course, the keyword list keeps growing and the test is that 18,000 of 23,000 records get the code identifiers I wanted, and not all records need that code, just a good majority.

So thanks again for the input
See us operating at http://www.austgov.com