Newbie Help! Primary Key not carrying through tables

[jonwitts]

I have constructed a DB for work to record accidents, however I am running into problems. On each form I have a button that Saves the data closes the current form and opens the next form, depending on what data was entered. I have this ruuning fine using this code,

Private Sub SaveRecord_Click()

Dim NextForm As String

If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If

DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm
End Sub


Private Sub SaveRecord_DblClick(Cancel As Integer)

Dim NextForm As String

If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If

DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm

End Sub

The primary key of this form is "PersonKey" and the two forms taht could open, each have"PErsonKey" as a foriegn key. However the key does not get brought across into the next form and the save function does not appear to work on all forms...

I put another button on this form using the control button wizard, and it gave me this code,

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub


However when I run this from the form, it tells me that the "PersonKey" field needs a value....

 

[ColinEssex]

Its because you're closing the first form, so where would the second form get the PersonKey from?

Col

 

[jonwitts]

Close form

Colin,

Thanks for your quick reply,

I have removed the DoCmd.Close from the code but I still get exactly the same problems, it does not bring the "PersonKey" over from the first table, and the from refuses to save until it has the "PersonKey".

I could have the user entering the key on every form, but this would open the database up to all sorts of user error, there must be a way to automate the primary key becoming the foriegn key in the next form / table.....

 

[KeithIT]

Either store your PersonKey as a variable before closing the first form and then set your ID field equal to that variable when you open the second form, or, if this is not a new record, you can open the second form and have your link criteria be "[PersonKey] ='" & Me.Personkey & "'" (remove the single quotes if your personkey is an integer rather than a string)

See if that helps.

Edit: Not sure how much you know about access (although you seem pretty comfortable with it) so I figured I'd post this just in case:

 Dim stDocName as String
Dim stLinkCriteria as String

stDocName = "Second Form Name"
stLinkCriteria = "[PersonKey] =" & Me. PersonKey (remember to use the single quotes if this is a string and not an integer)

DoCmd.OpenForm stDocName, , ,stLinkCriteria

 

[ScottGem]

I'll jump in here with some points.

1) Forms don't have PKs, tables have PKs. A form can be bound to a table. but the PK is still in the table and not the form. Along with this you don't have fields on a form, you have controls that may or may not be bound to a field in a table.

2) I would not store a status as Pupil/Child. Instead I would have a lookup table with an autonumber PK and store the PK as a StatusID FK. This will improve performance in links.

But neither of those two points addresses your question. Keith gave you one answer and its definitely a workable solution. It doesn't require leaving the first form open, it just applies a filter as you open the second form.

But I generally use a different solution. The thing that I don't like about applying a filter is that, if you want to use the next form to browse other records, you have to clear the filter first. My solution uses ther OpenArgs argument of the OpenForm method. You set OpenArgs to the control holding the PK using Me.controlname.

In the On Open event of the second form, you use code like:

If NOT IsNull(Me.OpenArgs) Then
     Me.RecordsetClone.FindFirst "[RecordID] = " & Me.OpenArgs
     Me.Bookmark = Me.RecordsetClone.Bookmark
End If

You would replace RecordID with the name of the PK field

What this does is position the record matching the passed PK as the current record displayed on the form. But the user can cycle thru records using the navigators.

 

[jonwitts]

Thank you for your prompt replies. I will try and impliment these solutions.. I will let you know where I get to...