PK Field (Long Integer) from Form 1 to Populate FK Field (Long Integer) Form 2?

[Hargo]

I have a Patient data entry form (PK is NHS Number) and an Admissions data entry form (FK is NHS Number)

Admissions is NOT a subform of Patient due to its size (it uses tab control and has 3 pages)

When Admissions form opens (upon Patient form Close) I would like NHS Number from the Patient form to Autofill NHS Number in the Admissions form and the focus to go straight to the next field (Patient ID - this is hospital reference)

I have sorted the Focus on Patient ID part but my code for the autofill doesnt work

Private Sub Form_Load()
Me![NHS Number] = " & Forms![frm_Patient_Data_Entry]![NHS Number]"
End Sub

I am a complete novice with VBA so the above code is just my 'best guess'

I have looked at various similar threads but they don't seem to be quite what I need


Thanks

[Jim Doherty]

You can use the OpenArgs property of the 'Admissions' form to pick up on the value passed to it by any calling form.

How you are navigating between the two really is down to flavour of your design. The idea here could be for the Admissions form to test its 'OpenArgs' form property setting when it opens up, if it finds a value passed to it, then perform a sequence of logic. Look at and research the 'OpenArgs' property to see how this can be used.

This is not the only way to do this I hasten to add. If it were me doing this I would primarily be opening the admissions form from a command button or similar mounted on the patients form and setting the logic of how the admissions form opens in he first place, whether that be to 'add a new record' or 'edit an existing record'.

Once the admissions form had opened successfully via the restrictive criteria applied in the DoCmd statement (look at those arguments) only then would I be closing the patients form and not until.

The DoCmd command already has the built in arguments as part of its structure to cater for all of this

[jforbes]

I wouldn’t always recommend this for a process that you want to lock down!

But, for Primary Keys and Foreign Keys on loosely associated Forms, I like to use a Global Variable and a get Function. But in the Spirit of Access you could use TempVars.

At any give point in time that you come across and interesting PK in Form1, Like the Current Event, set a Temp Variable to the PK:

Private Sub Form_Current()
    TempVars.Add "NHSNumber", Me![NHS Number]
End Sub

Now on Form2, for your FK field, you can set the Default Value Property to: =TempVars("NHSN umber")

Now as you navigate through records on Form1, the TempVar will be populated, and when you navigate to the New Record on Form2, it will Default to the TempVar.

[Hargo]

Thank you Jim Doherty & jforbes

OpenArgs looks a bit complex so I'll try The TempVars I think

Just one question for jforbes:

Should "NHSNumber" be "NHS_Number " due to my poor naming convention?

Or is it a 'created' value/name?

Regards

Hargo

[Hargo]

Just tried the TempVars and got an error message (see attached)


does this mean I have to get my head round OpenArgs?!?!

[imgnothumb] http://bytes.com/attachment.php? attachmentid=78 74[/imgnothumb]

[twinnyfo]

Hargo,

Try this:

TempVars.Add "NHSNumber", Me!(NHS Number).Value

I have encountered the same problem with TempVars. I know they have their value and can be very useful at times, but I have found TempVars can be finicky, temperamental and a bit clumsy.

And, concerning Post #4,

Should "NHSNumber" be "NHS_Number " due to my poor naming convention?

I always use strict naming conventions in my Tables/Queries and never use any spaces, dashes or underscores. It just makes it easier in the long run.

[Hargo]

Thanks again twinny but I 'got my head round' OpenArgs and it works!!!

However as the NHS Number is already in Admissions when it opens, I would like the focus to go straight to Patient ID (the next text box)

I tried The obvious (to me at least!!) option which was to use the form's open event but it seemed to interfere with the Open args code, specifically this part went yellow:

Me(strControl) = lngID

I then tried the NHS Number On Dirty event but that did nothing at all

At least I'm learning what NOT to do!! haha

[twinnyfo]

I am not sure what strControl is supposed to be referring to....

However, you can set the Tab Order of your controls. Since you will not be modifying the NHS Number, remove the Tab Stop from that control. Then move the Patient ID to the top of the Tab Order (Right click on the black square in the top left corner of the Form in Design View and select Tab Order).

[zmbd]

Hargo

Very simple ACC2010 database attached.
Form frm_people

This is only one way and a very simple method for using the openarg method.

The form frm_people should open when the database opens.
Select a record
Click on the command button
Opens the frm_data, the fk_people field should match the people_pk field
Trapping for no-match allowing for the user to create a new record and moves the focus to a data control
If the record is found the I move the focus to the stoptime control.

As I said, very simple. Now days, I tend to use custom events to keep two related but separate forms sync'd.
-z

[Hargo]

Only just seen this zmbd plus don't have zip at work so will have to have a look at home


Appears to be what I need given the no-match trapping idea

Thanks