473,320 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

88 64KB
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Me![NHS Number] = " & Forms![frm_Patient_Data_Entry]![NHS Number]"
  3. 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
Sep 17 '14 #1
9 1317
Jim Doherty
897 Expert 512MB
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
Sep 17 '14 #2
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     TempVars.Add "NHSNumber", Me![NHS Number]
  3. End Sub
  4.  
Now on Form2, for your FK field, you can set the Default Value Property to: =TempVars("NHSNumber")

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.
Sep 17 '14 #3
Hargo
88 64KB
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
Sep 19 '14 #4
Hargo
88 64KB
Just tried the TempVars and got an error message (see attached)


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

Attached Images
File Type: jpg TempVars Error.jpg (22.2 KB, 188 views)
Sep 19 '14 #5
twinnyfo
3,653 Expert Mod 2GB
Hargo,

Try this:

Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 19 '14 #6
Hargo
88 64KB
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 19 '14 #7
twinnyfo
3,653 Expert Mod 2GB
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).
Sep 19 '14 #8
zmbd
5,501 Expert Mod 4TB
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
Attached Files
File Type: zip BytesThread_958621_OpenFormSetFocus.zip (297.7 KB, 71 views)
Sep 20 '14 #9
Hargo
88 64KB
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
Sep 26 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: John Davis | last post by:
I want to know what's the differences between Request.Form("Field Name") and Request.QueryString("Field Name") OR they function exactly the same, which is to return the value of the field?? ...
5
by: Bunyip Bluegum | last post by:
I have a text field in a form which I need to check to see that only a 4-digit integer has been entered. The field has MAXLENGTH=4 and I'm using this to check for length: function...
14
by: jlemay | last post by:
if you look at http://www.emrose.net/contact1.html the last field is a textarea field and I can't make it align with the other text fields. it's pretty close, but this comes from trial and...
3
by: Gwin | last post by:
I want to create a select query where I can define which field is used by a control in a form. For instance if I have a table tServices, in which there are fields ServiceDate, ServiceType,...
4
by: Cath Victor | last post by:
Hi... I have a windows form that calls/uses a class to do some processing. I want this class to return values or append text to the form's textbox as it processes data. Any of you guys know how...
2
by: MLH | last post by:
Fields in MyTable: PostID PostDate RollQtyXfer RollDenomination RollCount37 RollCount23
3
by: jaishu | last post by:
Hi, I have a form where user enters an ID in a text box, the ID is actually 10 digits, but even if the user enters 5-6 digits , my code is supposed to pick tht and based on tthis it should...
8
by: gurmet | last post by:
I have two tables; 1. Called "Courses" that contains two fields "Course ID" and "Course Name". 2. Called "Sessions" that contains a field called "Course ID". Then I have a form with a...
1
by: ramel | last post by:
Hi all, I have a problem related to javascript , ajax and jsp. Please see this url http://xil.co.in/print_screen_2.JPG. I am working on this form. There are some buttons (like submit ,...
19
by: jaad | last post by:
how do you reference a single value field to a multi-value field? I sometime use a macro in form1 to open form 2 containing the same ID example: Open form: WorkOrder where condition: ="="...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.