Code returns incorrect records/fails to update form correctly

Question

So I'm going to try my best to explain the background to clarify understanding of what I'm hoping to achieve. I have a form (Events) that has a tab control, the tab control has 3 different pages each with a different subform on.

One of the subforms (speakers) has a button control that checks the 'speakers' attendeeID and if there is a match opens the 'Onboarding' form to the record that matches.

What it is suppose to do if the record doesn't match is open form to a new record and insert the ContactID, AttendeeID and EventID.

This is the 4th iteration of the code and works to a degree/doesn't return errors

Dim myR As Boolean
Dim strSQL As String
Dim myV1 As Integer
Dim myV2 As Integer
Dim myV3 As Integer

'Define the variables to be input
myV1 = Me.AttendeeID
myV2 = Me.ContactID
myV3 = Me.EventID

'Is there a Matching Onboard Record for the current Attendee Record?
myR = DCount("*", "tbl_Onboarding", "[AttendeeID] = " & Me.AttendeeID) > 0

If myR = True Then
  DoCmd.OpenForm "usf_Onboarding", acNormal, , , acFormEdit, acWindowNormal     'Edit Mode
Else        'No Matching Record, so Add one
  If Me.Dirty = True Then Me.Dirty = False      'Save pending Edits
  DoCmd.OpenForm "usf_Onboarding", acNormal, , , acFormAdd, acWindowNormal      'Add Mode

  Forms!usf_Onboarding!txt_ForceFocus.SetFocus

  strSQL = "INSERT INTO tbl_Onboarding (AttendeeID,ContactID,EventID) VALUES (" & myV1 & ", " & myV2 & ", " & myV3 & ")"

  Debug.Print strSQL

  CurrentDb.Execute strSQL, dbFailOnError

  Me.Requery

  DoCmd.Close acForm, "usf_Events", acSaveYes

End If

Using immediate I have confirmed myV1, myV2 and myV3 are all holding the correct values, and I used breakpoints to check line by line and all seemed to run okay with zero errors thrown back but I still have the following issues depending on whether myR is True/False

If the record exists (myR = TRUE) then something is now causing it to only load the 1st record of the table (Not the one where the attendeeID matches).
If the record does not exist (myR = FALSE) after running this the table has the new record in with the contactID/AttendeeID/EventID but the form doesn't update to show this (The form controls are set to locked = False) This results in an error when closing. So choosing not to save the data (Clicking yes on the box to discard changes) allows the force exit but the record/data IS saved with contactID/AttendeeID/EventID

Debug.Print strSQL also didn't do anything weirdly.

Castiel Castiel · Accepted Answer · 2019-04-18T11:29:14

Issue 1. "If the record exists (myR = TRUE) then something is now causing it to only load the 1st record of the table (Not the one where the attendeeID matches)."

I needed to add a where clause to the openform command

DoCmd.OpenForm "usf_Onboarding", acNormal, , "AttendeeID = " & Me.AttendeeID, acFormEdit, acWindowNormal

Issue 2. "If the record does not exist (myR = FALSE) after running this the table has the new record in with the contactID/AttendeeID/EventID but the form doesn't update to show this (The form controls are set to locked = False) This results in an error when closing. So choosing not to save the data (Clicking yes on the box to discard changes) allows the force exit but the record/data IS saved with contactID/AttendeeID/EventID"

I was loading the form before creating the new records and trying to then requery another new record not the one it created. All I needed was add record first then use the open code above to load that record instead

Else        'No Matching Record, so Add one
  If Me.Dirty = True Then Me.Dirty = False      'Save pending Edits

  strSQL = "INSERT INTO tbl_Onboarding (AttendeeID,ContactID,EventID) VALUES (" & myV1 & ", " & myV2 & ", " & myV3 & ")"

  CurrentDb.Execute strSQL, dbFailOnError

  DoCmd.OpenForm "usf_Onboarding", acNormal, , "AttendeeID = " & Me.AttendeeID, acFormEdit, acWindowNormal

Code returns incorrect records/fails to update form correctly

1 Answers