How to link forms

[jetb2]

Trying to create an access database with two forms. It would normally be 1 form with a subform but we're creating a test for new hire applicants to see if they can click buttons.

I have the first form setup to enter data about an officer. The second form is to enter events about that officer. I'm trying to link them by badgenumber.

I can have the officer form open the event form and vice versa but I can't link them ala subform. I also want the event form to display the officer in question and have it be unchangeable. Which I can do in a subform...

Thoughts? Suggestions?

 

[TheAceMan1]

How are ya jetb2 . . .

Your table structure and its relationships, dictate what you need to do with the forms.
[ol][li]MainForms are always based on parent tables.[/li]
[li]subForms are always based on child tables and [blue]are embedded on the parent form[/blue]. When used as a subform the parent primarykey and the child foreignkey typically synchronize the form. This is done automatically if the form wizard is used to setup parent/mainform with child/subform.[/li]
[li]Any opened form that is not imbedded on a parent form, is an [blue]independent form[/blue]. When opened this way there are no [blue]link master/child properties![/blue] Synchronizing is usually done with criteria in the query/sql of the recordsource.[/li][/ol]
Do provide some assemblence of your tables and relationships. In the meantime perhaps faq702-5860 will be of some assistance.

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and gives insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[fneily]

a test for new hire applicants to see if they can click buttons". I'm giving a talk at Johns Hopkins on the sad state of affairs the U.S. educational system is in and wonder if I can use your line?

You can use the Openform command. Just be sure to keep the first form opened.
Private Sub Command8_Click()
Dim stroffice As String
DoCmd.OpenForm "frmEvents", , , "[badgenumber] = " & forms![frmOfficer]![badgenumber]
End Sub

Highlight Openform and hit F1 for more info.

forms![frmOfficer]![badgenumber]
explanation: the first part Forms is called a container. It contains the names of all OPEN forms.
the second part is which form you want to reference.
the last part is the name of the control on that form.

 

[jetb2]

Thanks TheAceMan1, that was a big step forward. I have it so I fill in my Officer form and then click my button to add events for the officer. Because of the query it asks me to enter the badge number of the officer I just entered instead of linking me directly to that officer. I don't want them to have a choice, merely have them given the events for the officer just entered.

Although on a positive note it now shows all the officer details I wanted on the event form!

 

[jetb2]

Here's the form in question.

 

[TheAceMan1]

jetb2 . . .

Had a good look at the Db. We'll fix the problem, afterwards I have some advice.

The problem is two fold and is centered around the recordsource query for [blue]frmEvents[/blue]:
[ol][li]You set criteria for [blue]strBadgeNumber[/blue] to

[blue][Forms]![tblOfficer]![strBadgeNumber][/blue]

The formname [blue][tblOfficer][/blue] is wrong. It should be [blue][frmOfficer][/blue].[/li]
[li]Now that the formname is corrected the 2nd problem arises. Your referencing the form [blue]frmOfficer[/blue], however, your macro [blue]mcrOpenfrmEvents[/blue] closes [blue]frmOfficer[/blue] before you open [blue]frmEvents![/blue] . . . [purple]You can't reference a form thats closed![/purple][/li][/ol]
To fix this:
[ol][li]Correct the formname as shown previously.[/li]
[li]In the macro [blue]mcrOpenfrmEvents[/blue], remove the [blue]Close[/blue] action line. Save & close the macro.[/li]
[li]In the [blue]On Load[/blue] event of [blue]frmEvents[/blue], set a macro to close [blue]frmOfficers[/blue].[/li]
[li][blue]Done![/blue][/li][/ol]
Instead of flip-flopping between two forms, all you really need is frmEvents (get rid of frmOfficers and rename frmEvents the same. You'll also have to remove the criteria for strBadgeNumber in the query. With this setup you can hide the subform when you add a new officer and show the subform when the record is saved.

The above will also allow you to browse thru officers with the navigation buttons. You could also add a combobox of officers names that takes you right to their record.

Your use of macro's shows a lacking in VBA so I stuck with that. However, if your going to take leaps and bounds, get into VBA as fast as you can.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jetb2]

Thanks. I actually _do_ want to be flip flopping between forms. It's supposed to be a test for secretarial applicants to see if they can do simple data entry and go between forms.

I have made the basic changes you specified (thank you). I now have it so that when I enter a new officer and click add new event it gives me a blank entry form with no link to the previously entered officer. But if I choose a previously entered officer (click back one record) and then click add event it shows the chosen officer. Something's not right with the saving of the officer form?

 

[fneily]

TheAceMan1 - thanks for reiterating what I posted. Just bugging you, buddy.

 

[TheAceMan1]

jetb2 . . .

Open frmOfficer in design view then:
[ol][li]Remove the forms OnCurrent event.[/li]
[li]In the OnOpen event remove all code lines except:

[blue]DoCmd.GoToRecord , , acNewRec[/blue]

[/li]
[li]In the OnClick event of the button remove mcrOpenfrmEvents from the event line. Then click the dropdown list and select [Event Procedure].[/li]
[li]Then in the code for the button, make the first line:

[blue]    DoCmd.RunCommand acCmdSaveRecord[/blue]

Also remove:

[blue]    DoCmd.Close[/blue]

[/li][/ol]
Give it a whirl! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[jetb2]

That seems to have done it. Now I have to go back through and see what does what. Since I did it so piecemeal I'm not sure what commands do what in the code.