Help: Linking Two Forms

[jagstangman]

Hi, I have a question.

I have a form with details of a people on (Such as forename, surname and the such like)

I also have a button to open another form (which pops up on top of the original form). On this form is other details (Such as Medical Details)

No matter what record im viewing on the original form the details on the medical form, when opened, doesn't change corresponding to what record is being viewed on the original form and only shows the first record all the time.


My question is this:

Is there a way so when the button is clicked to open the medical form, the data shown on that form is for the person/record being viewed on the original form.

Thanks

 

[ansentry]

You don't say what the relationship is between the two forms. Are they main form and subform ( 1 person can have many medical details).

To link them try using the wizard;

Put a command button on your, select Form Operation, open form - Next
Select the name of the form you want to open -Next
Select first option (Open the form and find specific data to display) - Next
Now select the key that links the two forms then click <..> - Next
Your choice then finish.

Any problems post back, maybe with a sample of your db.

 

[jagstangman]

Yeah it is a form and sub form im wanting.

It's worked, I just wanted to stay away from the wizard because iv already designed and made the two forms. But i suppose i can quite easily change the layout of the new forms.

Cheers

 

[ansentry]

it's worked, I just wanted to stay away from the wizard because iv already designed and made the two forms. But i suppose i can quite easily change the layout of the new forms.

You don't have to change the design of your form, put the command button in the Form Header or Form Footer.

Have a look at my sample I posted <<HERE>> It will allow you to move through records and also add a new records to the subform.

 

[mic907]

great advice, but how do you make the value of a key carry over?

to clarify:
I have a 'Consultant' form with a ID number as the primary key (and is autonumbered). I would like a button to not only open a form (in my case, a 'Contract' form), but also automatically carry over the ID number from the 'Consultant' main form. This is to tie the new 'Contract' form entry to that particular Consultant. What do I need to do to accomplish this?

 

[boblarson]

DoCmd.OpenForm "YourFormNameHere", acNormal, , "[YourFieldOnTheNewForm]=" & Me.YourControlNameOnTheFormYouAreOpeningFrom

 

[mic907]

thanks boblarson but no go. the ID number doesn't carry over to the form im opening.

 

[boblarson]

Post the exact code you're using to open the form AND, the names of each of the forms and the name of the control that is on the first form that contains the ID number.

 

[mic907]

Private Sub Command54_Click()

DoCmd.OpenForm "Contracts", acNormal, , "[ConsultantID]=" & Me.ConsultantID

End Sub


Main Form: ConsultantList
Form (being opened): Contracts
Related Field: ConsultantID (same name in each table; I need the value of this in the main form (which is autonumbered) to be carried over into the 'Contracts' form under the ConsultantID field

 

[boblarson]

DoCmd.OpenForm "Contracts", acNormal, , "[ConsultantID]=" & Me.PutWhateverIsTheControlNameThatHousesConsultantIDonYourMainFormHere

 

[mic907]

Me.PutWhateverIsTheControlNameThatHousesConsultantIDonYourMainFormHere

I believe that 'ConsultantID' is the name....is it a problem that I used the same name for two fields in separate tables?

hm...are you giving me the right code to CREATE a new record under the 'Contracts' form with the ConsultantID carried over? perhaps I didn't clarify that was what I needed...because when the current ConsultantID number on my main form is already existing in my 'Contracts' table, it will go to the appropriate record (with the correct ConsultantID). However, say when I create a new record under "ConsultantList" and 'ConsultantID' is autonumbered with a value NOT present in the 'Contracts' table, the value is not carried over and the resulting ConsultantID on my newly opened 'Contracts' form says "0".

hope i clarified things....thank you again for your time.

 

[boblarson]

no, but you may want to change the text box that is bound to ConsultantID on the main form to txtConsultantID and then the code would be:

DoCmd.OpenForm "Contracts", acNormal, , "[ConsultantID]=" & Me.txtConsultantID

 

[mic907]

check out my last post...i just edited it

 

[boblarson]

you are correct, the code I posted will open the form to an existing record. To open to a new record and place the id number in would entail something different.

To add the new record with the ID number set in the text box for ConsultantID on the second form, you have several options.

If you are just opening the 2nd form without specifying a new record, and you just want it to know if it doesn't already exist, then you'll have to check for the existence first (probably with a DLookup) and then if that returns no record then have the code open the form first and then just use

Forms!Your2ndFormName.txtConsultantID = Me.txtConsultantID

to set the number in.

If the DLookup returns a record, then use the other code we already posted.

 

[mic907]

sorry but could you possibly write the code for me if it doesnt take too much time? I have no idea how to use dlookup and am not very knowledgeable about 'if' statements in VB (at least, thats what I think your implying I use)

 

[mic907]

also, another problem with this. the code you gave me above actually manages to carry over the consultant ID, but if i want to create a new record under the contracts form, the ConsultantID on the contracts form sets itself back to "0". How do i make it stay put?!

sorry this is driving me a little nuts, as everything works through subforms so much more easily. However I can't just use subforms within a form, as I have a lot of fields for each table/form and so is not a preferable option.

Also, I'm not sure if this is possible, but I was wondering if anyone knew if Access lets you view the code that it automates through the wizard. perhaps if you can, someone can tell me how to get it from my subform structure and somehow apply it to my wanted "linking forms" structure. just a thought, and thanks again in advance for any suggestions.

 

[mic907]

any help????

 

[mic907]

can someone please take a look at my problem? I'm sure there's a simple solution to this, I just can't figure it out

 

[ansentry]

Your posted:

great advice, but how do you make the value of a key carry over?

You don't say who you were addressing when you posted this so I am assuming that you have looked at my sample and then asked the question, "how do you make the value of a key carry over?"

I think it is a good idea to address your replies to whom you are asking the question of.

When you click the button (Open Form) on the Main Form (FrmMainLink) the following code runs:

 Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSubLink"
    DoCmd.RunCommand acCmdSaveRecord
    
    stLinkCriteria = "[MainFK]=" & Me![MainID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Me.Refresh

If you scroll through the records from the main form then the following code keeps them in sync.

   If Me.NewRecord = True Then
         Me.Caption = "New Record"
   Else
         If CurrentProject.AllForms("frmSubLink").IsLoaded Then
         DoCmd.OpenForm "FrmSubLink", acNormal, "", "MainFK=Forms!FrmMainLink.Form.MainID", acEdit, acNormal
         End If
         Me.Caption = Me.CusName
   End If

That all works fine until you try to add a new record to the subform (frmSubLink) to over come this I use the following code in the On Current Event of the form (frmSubLink).

[Dim iMain As Integer
iMain = Forms!frmMainLink.Form.MainID
    
If Me.NewRecord = True Then
    If MsgBox("Do you want to create a new record linked to the current ID?", vbYesNo + vbExclamation + vbDefaultButton2, "WARNING") = vbYes Then
    Me.MainFK = iMain
    Me.Caption = "New Record"
    End If
Else
    Me.Caption = Forms!frmMainLink.Form.CusName
End If

I have just noticed that the "Open Form" button is still able to be used if you are on a New Record. This would require code to disable the button or msgbox warning that you can't open a linked record while the current record is blank.
.........you should be able to fix that bit yourself.