One-to-many problem, multiple row per record

[jzwp22]

What happens when two people have the same first and last name?

It is better to use the primary key value associated with the person since it will always be unique. Include the primary key in the combo box as the bound field as well as in your report and set up a grouping level on it.

 

[lostwithaccess]

I tried using the primary key, which alerted me to a problem - for those staff with dual nationality my form has recorded them person with two records - one for each nationality.

In my data entry form there is a subform which records nationality, and if it is dual then you click cmdDualNationality (macro; go to record - new) and enter a second.

Not sure why it is making a new staff record for each nationality?

 

[lostwithaccess]

Thinking I may have a relationship wrong I checked a few different staff records to find that not all of those staff with dual nationality have been recorded as 2 staff. tblStaffNationality shows the same pkStaffID for 2 nationalities -which is correct, whereas for a couple of others it shows two pkStaffID's... confusing

I'll try deleting the problem records and reentering to see if that clears it up, so that I can then use the StaffID as the bound column of my combo. Still not sure how this could have happened and if it could happen again?

 

[jzwp22]

What is the record source for the main form?

and if it is dual then you click cmdDualNationality (macro; go to record - new) and enter a second.

Is this command button on the main form or the subform?

 

[lostwithaccess]

The record source of the main form is tblStaff

And the subform Mast is PkID, Child fkID

The macro is a command button on the subform also.

I checked the whole dataset and it was only the details for one staff member that were duplicated with the different nationality so I deleted and reentered the details and it now seems ok. Still not sure why the error occurred though

 

[jzwp22]

You seem to have everything set up correctly, so it is hard to say what caused the error. I would just probably do some more testing (entering new records in both main & subforms etc.) to see if you can duplicate it. If not, then you might be ok.

 

[lostwithaccess]

I have been trying to change my combo box (searching by last name) so that the bound column is the primary key rather than the last name, but I have been unable to successfully change the filter code to work with this.

My main form holds pkStaffID, which I want to match to the combo box selection.

Current onclick event for cmdFindByLastName is:

Private Sub cmdFindByLastName_Click()
DoCmd.OpenForm "frmViewEdit", acNormal, , "pkStaffID='" &  Me.cboLastName.Column(1) & "'"
DoCmd.Close acForm, "frmSearchByLastName"
End Sub

Combo box row source:
SELECT qryLastName.pkStaffID, qryLastName.txtLastName, qryLastName.txtFirstName FROM qryLastName ORDER BY qryLastName.txtLastName;

Bound column: 1. Column Count: 3

I'm not sure what is going wrong?

 

[jzwp22]

Since the bound field is numeric, you should not enclose the value from the combo box with single quotes (single quotes are used for text fields not numeric fields). I have shown the single quotes in red below; you just need to get rid of them.

Also, regarding combo boxes, Access starts counting columns at zero not 1. It is correct to set the bound column property of combo box to 1 but when you reference the combo box either in an expression or in code, the bound column is column 0.

Private Sub cmdFindByLastName_Click()
DoCmd.OpenForm "frmViewEdit", acNormal, , "pkStaffID=[COLOR="Red"]'[/COLOR]" &  Me.cboLastName.Column([COLOR="Red"]1[/COLOR]) & "[COLOR="Red"]'[/COLOR]"
DoCmd.Close acForm, "frmSearchByLastName"
End Sub

Also, when you are referencing the bound column of the combo box, you do not need to explicitly provide the column #. With that, the code should look like this:

Private Sub cmdFindByLastName_Click()
DoCmd.OpenForm "frmViewEdit", acNormal, , "pkStaffID=" &  Me.cboLastName
DoCmd.Close acForm, "frmSearchByLastName"
End Sub

 

[lostwithaccess]

When I opened the database this morning, and went to enter data into a subform, I was given an error message saying something along the lines of "link master fields return without gosub" but after closing the error message the problem went away, and even after further open/closing of the db it has not reoccurred. Does not appear that I need to do anything now, just seems strange?

One small point I have picked up in testing is that in my combo box filter, if no selection is made then cmdfilter is clicked, I get the error "Syntax error (missing operator) in query expression 'pkstaffID='

Is there a way to add something to the following code so that a text box appears telling you to make a selection, rather than it giving an error? It works fine if you make a selection. Current code is:

Private Sub cmdFindByLastName_Click()
DoCmd.OpenForm "frmViewEdit", acNormal, , "pkStaffID=" & Me.Combo25
DoCmd.Close acForm, "frmSearchByLastName"
End Sub

Many thanks for the previous code help, worked perfectly!

 

[jzwp22]

Regarding the error, you might want to compile the database. This is done via the VBA window Debug on the tool bar. If there are any errors in your code, the compile will point them out.

In terms of supplying a message, something like this should work:

Private Sub cmdFindByLastName_Click()

IF nz(me.combo25,"")<>"" THEN
   DoCmd.OpenForm "frmViewEdit", acNormal, , "pkStaffID=" & Me.Combo25
   DoCmd.Close acForm, "frmSearchByLastName"
ELSE
   msgbox "You must make a selection"
   me.combo25.setfocus
   Exit Sub
END IF
End Sub