Primary Key and Combo Box not synchronised

[Steven19]

I have the following table:

tblTypeofIncident
TypeofIncidentID - Primary Key
TypeofIncident - Text



I have a form just for this one table.

The user should from a combo box select the type of incident, and click continue, my control button does a docmd, picks up the ID and takes it to the Foreign Key of the following form.

However, the function works for the first value in the combo, but when selecting the second value, the value changes in the combo box, but the primary key doesn't change with the selection. All my code does then is to rename the text in the TypeofIncident field to value 2. I dont have issues with other ways of doing it, but I'm scratching my head over what should be something simple.

Private Sub Closebtn_Click()
DoCmd.OpenForm "FrmReportingAccident", , , "[TypeofIncidentID] = " & Me![TypeofIncidentID], , , Me!TypeofIncidentID
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

[Micron]

I don't get that trailing part and am surprised if you're not getting an error. Why not
DoCmd.OpenForm "FrmReportingAccident", , , "[TypeofIncidentID] = " & Me![TypeofIncidentID]

If the PK isn't changing then it must be because the control isn't updating but I would think that moving focus to a command button should take care of that. Step through your code and check the value of the control. Also recommend that you use Me. not Me!

[ssanfu]

Also note that ", acSaveNo" is only for saving changes in the form design.....you have not changed the form design, so it is not needed.
I use

DoCmd.Close acForm, Me.Name

with nary a problem.

[Steven19]

Hi

I've attached the database, Open MainMenu, and click New Accident / Injury / Near Miss this is how simple it should be.

I have cleared lots of test data, but the rest of the database works fine, its just this section.

Can you kindly have a look and see what the issue may be?

Regards

[ssanfu]

Try this:

In form "FrmTypeofIncident", the name of the combo box is "TypeofIncident" (NOT TypeofIncidentID).

In the code, you have

Private Sub Closebtn_Click()
    DoCmd.OpenForm "FrmReportingAccident", , , "[TypeofIncidentID] = " & Me.[TypeofIncidentID]
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

plus you do not set "OpenArgs"

The code would be

Private Sub Closebtn_Click()
    DoCmd.OpenForm "FrmReportingAccident", , , "[TypeofIncidentID] = " & Me.[TypeofIncident], , , Me.[TypeofIncident]
    DoCmd.Close acForm, Me.Name
End Sub

Next, I would make form "FrmTypeofIncident" an unbound form (delete the Record source value). The combo box does not need to be on a bound form since all you are doing is getting a value (the PK) from a combo box.
You will also have to clear the combo box "Control Source".

I don't understand why you are setting the default value of "[TypeofIncidentID]" in table tblAccidents (but I don't really need to understand ).



Also, on form "FrmTypeofIncident", I would change the names of the two buttons.
The button CLOSE I would rename from "Private Sub Command6_Click()" to "Private Sub Closebtn_Click()" and the button CONTINUE I would rename from "Private Sub Closebtn_Click()" to "Private Sub Continuebtn_Click()"

[Steven19]

Thank you.

That worked excellently. All suggestions taken on board and updated.

Much appreciated

[ssanfu]

You're welcome..