Opening a form from another form with an option button

[BroncoMarc]

I want to make several forms that will be very similar and use information from the same table. I would like to create a list of option buttons (one for each form) so the user can click on a button to switch to a different form. The other form would be another way look look at the same data.

How do I make an option button open a different form to the same record they're currently looking at, then close the original form?

Thanks,
- Marc

 

[Fuga]

I use checkboxes:

as an on_click event of a button:

If [checkbox1] = -1 then docmd.openform "form1"
If [checkbox1] = -1 then docmd.openform "form2"

etc

To open the same record, I think you can use this, but I´m not sure:

docmd.openform "form2",,,[fieldinform2]=[forms]![form1][fieldinform1]

Fuga.

 

[BroncoMarc]

I couldn't get that to work. I played with a lot of things, but just about everything made the 2nd form come up with a filter applied which I don't want. I just want the 2nd form to jump to the same record that's currently up in the first.

Here's the best thing I've got so far:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "form2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, stDocName, acGoTo, 5

This will open form2 and jump to the 5th record. What can I replace the 5 with that will refer to the record number that is currently being viewed in form1 ?

- Marc

 

[BroncoMarc]

Hey.. a little more playing and I came up with this:

stDocName = "Form2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, stDocName, acGoTo, Forms![Form1].CurrentRecord


Looks like this works!

- Marc

 

[JustMaybe]

hate to add the novice touch...but

I had the same problem, and rather than work with all the code, i simply used a button wizard
e.g i used the wizard to open a form , selected the form i wanted to use.. i then just selected to open that form linking the two primary keys on each form. this would open the next form on the same record. Then if you want only that record to be accessable just remove the record selectors and the scroll bars.

Could anyone explain why i'd be better off using the code??

 

[JustMaybe]

hate to add the novice touch...but

I had the same problem, and rather than work with all the code, i simply used a button wizard
e.g i used the wizard to open a form , selected the form i wanted to use.. i then just selected to open that form linking the two primary keys on each form. this would open the next form on the same record. Then if you want only that record to be accessable just remove the record selectors and the scroll bars.

Could anyone explain why i'd be better off using the code??

 

[BroncoMarc]

I tried that and it put a filter on the 2nd form so I could only access that 1 item.

I want to be able to go thru the records, decide I want to view them in a different form, select that form, then continue going thru records.

You can do a lot more with code.. I just need to learn it better.

- Marc

 

[Oldsoftboss]

Maybe a different tact...
Why not one form with a tab control. Set the tabs to none and put each 'form' on a different page. Then when pressing your option button move the focus from one page to the next

 

[BroncoMarc]

Wow.. Thanks.. Much better solution. I've never played with tabs. I didn't even notice them on the toolbar.

With tabs I can change between pages and it always stays on the same record no matter which page I'm on. Don't have to worry about syncing them.

Saves a lot of duplication of work than if I used multiple forms.

- Marc

 

[Oldsoftboss]

Glad to help. Had another thought after I posted that you could do away with the option buttons and just set the tab pages to either tabs or buttons.
Good luck
Dave

 

[BroncoMarc]

This was working great untill I started populating the other tabs with controls.

I've got about 50 text boxed, and there will be 10-15 tabs. I tried pasting the text boxes to the 6th tab and it gives me an error "Microsoft Access can't create any more controls on this form or report. If you have deleted controls from the form or report in the past, you may be able to rename the form or report and then add more controls to it."

I tried renaming it and it didn't help. I went into help and found "Number of controls and sections you can add over the lifetime of the form or report : 754"


Do the labels for the text boxes count? If so, I can see how I could be close to that limit. Man, I guess I might have to put all the text boxes that are common to all the tabs 'behind' the tabbed pages, but that's going to severely limit the way I can arrange things on each of the tabbed pages.

Is there any way around this?

Thanks,
- Marc

 

[Oldsoftboss]

Not sure if I'm helping here or not, but you may try creating a new data base and importing the form, not sure if this will reset the no of used objects on a form or not.
Any suggestions?
Dave

 

[BroncoMarc]

Ok.. new approach.. Tabs aren't going to work. I'll never get under the max objects on a form limit.

So, I decided to try using subforms. I made a 'parent form' with a subform on each tab. The only objects on the parent form are the tabs, subforms, and a reference number from the table to keep track of what record I'm on.

My next problem.. I've got the main form and sub form syncronized, but the sub form always shows just 1 record. I can use the controls on the parent form to move the sub form to other records, But I've got buttons I added to the subform for next, previous, find, etc. I modifided to code for next and previous so it controls the parent form, but I can't get find to work. It only searches the 1 record.

The real fix is to make the sub form show the same nuber of records as the main form. How do I do this?

Can you tell I'm kind of new to Access?? I just love getting in over my head. Thanks for all the help you guys have given!

- Marc

 

[Oldsoftboss]

A form with over 750 controls on it ??
I think you need to rethink what you are trying to achive here.

You said in an earlier post that the second and third forms were a different way of looking at the data ??

Maybe look at your table structures and make several related tables rather than having a squillion fields in one table.

PS
I am still bemused why the access wizard wont achive what you started out to do. By stepping through the questions carefully when you create a command button you should be able to open a second form at a certain record.

Dave

 

[BroncoMarc]

Let me sum up what I'm doing.. We have an information form that someone is filling out for each of our products for each of our customers. She has been maintaining a seperate excel file for each of the forms. (about 6000 files)

I'm creating this database with 1 table for all the products (65 fields in the table). She'll enter the data on the form, then there are about 10 buttons she could hit, one for each customer. The buttons launch a report thru a query that selects only the current record in the form. There is a seperate report for each customer which shows the info the customer wants formatted the way they want.

Originally I was just going to do 1 form with every possible field (65). But then I thought I'd make it easier on her and make a form for each customer with just the info that's on their report. But, I want her to be able to jump between the forms easily.. so, If she's looking at a certain record, she could hit a button and choose a different customer's form, but the same record will be displayed.


Tabs looked like it was going to be an easy way to accomplish this, but with 50-65 fields per page I hit the limit pretty quick.

Trying to open a different form took too long and it always came up filtered to the record I had selected in the first form.

Using subforms looks promising, but the subform only shows 1 record, so I can't do a search.

What I want to do now is have just the primary key field on the main form, but hidden, and have all the other info on the sub forms. Using the controls on the main form works, but using the controls on the sub form only show 1 record. How can I make the controls on the sub form show all the records?

- Marc

 

[Oldsoftboss]

Code to go to record - take 1

 

[Oldsoftboss]

Using a "switchboard"