Adding record and populating fields in an unrelated table

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

Guest

Hi,

Here are my tables and some relevant fields:
- "HP" containing "MRN" (key) and "ID"
- "Med" containing "MedID" (key), "MRN" and "loop" (a text)
- "Research" containing "ID" (key) and "loop"

"HP" and "Med" have a one to many relationship. For complicated reasons, I
have not related Research with any other tables.

I have a form ("HPform") with its subform ("Medsubform" in datasheet view)
related by MRN.

This is what I would like to do:
1. Write a code in "loop" control of Medsubform so that when one puts data
in the "loop" control it looks up the related "ID" in the parent "HPform" and
checks to see if this record exists in the "Research" table.

2. If it exists, I want it to populate the "loop" field in the "Research"
table with the value in the "loop" control in the "Medsubform".

3. If it does not exists, I want it to create a new record in the
"Research" table with the value of "ID" in the "HPform" and then populate the
"loop" field.

I really appreciate your time in helping me with this.

Thank you,
 
Use the AfterInsert event procecure of the subform to execute an append
query to add the record to the Research table.

Use DLookup() to see if the record already exists. This kind of thing:

Private Sub Form_AfterInsert()
Dim db As DAO.Database
Dim strSql As String
If Not IsNull(Me.loop) Then
If IsNull(DLookup("ID", "Research", "loop = ''' & Me.loop & """"))
Then
Set db = dbEngine(0)(0)
strSql = "INSERT INTO Research (...
db.Execute strSql, dbFailOnError
'MsgBox "Added " & db.RecordsAffected & " record(s.)"
End If
End If
Set db = Nothing
End Sub

You can mock up an append query, using any literal values to get the sample
SQL statement. Then switch to SQL View to see an example of the kind of
string you need to create. Concatenate the values into the string.

Needs error handling.
 
Thank you very much...
This was extremely helpful!

I had to make some changes in the Lookup argument as I wanted to see if "ID"
already exists rather than loop. But it worked like a charm.

Thanks,
 
Back
Top