make sure data is entered before going to new form?

[AccessIdiot]

Like your mother-in-law that visits too often, I am back. :-)

I have two tables, two forms. The primary key of table 1 is a foreign key in table two. I have a button on form one that opens form two and it should take the ID autonumber field from the first form and populate the ID field on the second form (and get stored in the table too).

But it's not working. My guess is that technically the data from the first form hasn't populated the database yet so it can't get carried over to form two.

So how do I tell Access that when I click the button to open up form two, go ahead and stick all the data that was entered on form one into the database (but you don't have to go to a new record, just stay put while form two is being filled out - eventually it will be closed).

Is it like a requery or refresh or something like that?

thanks to any and all help,
melissa :-)

[Rabbit]

To set the join type, you can either change it in SQL view or you can double click the link and select the type of join you want.

[AccessIdiot]

So in a one to many relationship would you usually want a left or right join? Or does it just depend on the situation?

Oh and thanks again - it worked brilliantly!

[Rabbit]

So in a one to many relationship would you usually want a left or right join? Or does it just depend on the situation?

Oh and thanks again - it worked brilliantly!

Not a problem.

It depends on the situation. If the table is just there for additional information then you'd want to use a left/right join. But if the other table is what you're after then you'd use an inner join.

For example. Lets say I have a table of students and a table of comments about the trouble students. So I want a full roster of students along with the comments. In this case I would use a left join because it doesn't really matter if they have a comment or not, I want to return their record.

However, if I want to return only the students with comments, then I'd use an inner join.

[AccessIdiot]

That makes sense - thanks for the example.

Let me know if you figure out the Survey_ID problem!

[Rabbit]

That makes sense - thanks for the example.

Let me know if you figure out the Survey_ID problem!

The problem is being caused by your join. There's nothing to link those two fields together. Survey_ID is being linked on the Survey table and Replicate table but nothing to link the Gear_ID in your Replicate to your Gear table.

You'll either have to include the Gear fields in your table Replicate or make Gear a subform.

[AccessIdiot]

Okay now I'm confused. The Survey and Replicate tables are joined by Survey_ID. The Survey and Gear tables are unrelated. I did make the switch to a left join on the Replicate subform to account for it's relationship with the Gear table (there is a Replicate_ID in the Gear table) but that shouldn't have anything to do with the Survey/Replicate relationship.

Should it?

[Rabbit]

Okay now I'm confused. The Survey and Replicate tables are joined by Survey_ID. The Survey and Gear tables are unrelated. I did make the switch to a left join on the Replicate subform to account for it's relationship with the Gear table (there is a Replicate_ID in the Gear table) but that shouldn't have anything to do with the Survey/Replicate relationship.

Should it?

Sorry, I meant the Replicate_ID connecting the Gear and Replicate tables. Because there's nothing to populate the Replicate_ID field in the Gear table, the record can't be created because of their relationship.

Although, after more testing, I think it might be the simple matter that you can't update a query that pulls data from multiple tables. But the relationship between the Gear table and the Replicate table still remains an issue.

[NeoPa]

Although, after more testing, I think it might be the simple matter that you can't update a query that pulls data from multiple tables. But the relationship between the Gear table and the Replicate table still remains an issue.

Nice work again Rabbit :)
My turn to jump in quickly.
I'm not sure that queries can be updatable with outer joins (LEFT JOINs or RIGHT JOINs). A non-updatable query cannot be used as the record source of a form for modification (Add or Update). Typically, to use multiple tables in a record source of an update form, you need to connect them with an INNER JOIN.

[Rabbit]

Nice work again Rabbit :)
My turn to jump in quickly.
I'm not sure that queries can be updatable with outer joins (LEFT JOINs or RIGHT JOINs). A non-updatable query cannot be used as the record source of a form for modification (Add or Update). Typically, to use multiple tables in a record source of an update form, you need to connect them with an INNER JOIN.

But an inner join would be incorrect for the purposes of her subform. So it would seem that the Gear field will have to be a subform. Although from the design of the form it seems as if gear is a 1:1 relationship in which case you shouldn't need the extra table.

[AccessIdiot]

Not sure I totally follow the train of thought here (sorry, I'm kind of slow on the joins) but I do need the gear table to be separate because comments are stored only if the gear status dropdown was set to bad. Not every replicate will have a bad gear status so I only need to record those replicates that do, which is why I put the Replicate_ID in the gear table and not the gear_id in the replicate table.

Is that right?

[Rabbit]

Not sure I totally follow the train of thought here (sorry, I'm kind of slow on the joins) but I do need the gear table to be separate because comments are stored only if the gear status dropdown was set to bad. Not every replicate will have a bad gear status so I only need to record those replicates that do, which is why I put the Replicate_ID in the gear table and not the gear_id in the replicate table.

Is that right?

Not quite. By putting the Gear field in the same table as replicate, you can get the same functionality. You can disable and enable the field on a bad gear status. The gear that isn't bad will just simply be blank for those records.

[AccessIdiot]

Right, and that's how we had it originally. But we thought it was bad form to have empty fields (if all goes well with the gear there will be lots of them). We thought better to normalize further and separate it out to its own table.

[Rabbit]

Well, you can still do it that way but you'll have to make it a subform.

[AccessIdiot]

Okay, I can do that I think. :) And switch it back to an inner join? Won't that mess up the record navigation though?

And any idea about the Survey_ID and why it won't carry over from the Survey form to the Replicate subform?

[Rabbit]

Okay, I can do that I think. :) And switch it back to an inner join? Won't that mess up the record navigation though?

And any idea about the Survey_ID and why it won't carry over from the Survey form to the Replicate subform?

You won't use a join anymore. With gear as a subform on the replicate form you don't need a join.

The reason that the ID won't carry over was because of the join so you have to get rid of it and just set the recordsource of the subform to the table and then the ID will carry over.

[AccessIdiot]

Really? Because we're talking about 3 tables here: tbl_Survey, tbl_Replicate, and tbl_Gear.

tbl_Survey and tbl_Replicate share Survey_ID.
tbl_Replicate and tbl_Gear share Replicate_ID.

So you are saying that if I remove Gear_Comments from sbfrm_Replicate and make it its own subform within sbfrm_Replicate then that will solve the Survey_ID issue?

I will try it but I never in a million years would guess that that is the problem here.

Thanks for the suggestions!

[Rabbit]

Really? Because we're talking about 3 tables here: tbl_Survey, tbl_Replicate, and tbl_Gear.

tbl_Survey and tbl_Replicate share Survey_ID.
tbl_Replicate and tbl_Gear share Replicate_ID.

So you are saying that if I remove Gear_Comments from sbfrm_Replicate and make it its own subform within sbfrm_Replicate then that will solve the Survey_ID issue?

I will try it but I never in a million years would guess that that is the problem here.

Thanks for the suggestions!

You have to make gear a subform on the replicate form AND you have to get rid of the join. The join is causing the problem, not the relationship between replicate and gear. The ID doesn't carry over when you have a join in the record source of the subform.

[AccessIdiot]

OH MY GOD.

I can't believe it worked. I have been struggling with this for weeks.

*gets down on knees and bows up and down*

"We're not worthy! We're not worthy!"

Like I said, I never ever EVER in a million years would have made that connection. THANK YOU SO MUCH.

Now, two questions:

1) Do I need to do the same thing with the Entrainment table and make the Gear part of it a subform?

2) The survey form with the replicate subform is long and crowded. Is it possible to spread it out over two tabs and have the survey part on one tab and the replicate subform part on another tab with buttons that take you from one tab to the other?

[Rabbit]

OH MY GOD.

I can't believe it worked. I have been struggling with this for weeks.

*gets down on knees and bows up and down*

"We're not worthy! We're not worthy!"

Like I said, I never ever EVER in a million years would have made that connection. THANK YOU SO MUCH.

Now, two questions:

1) Do I need to do the same thing with the Entrainment table and make the Gear part of it a subform?

2) The survey form with the replicate subform is long and crowded. Is it possible to spread it out over two tabs and have the survey part on one tab and the replicate subform part on another tab with buttons that take you from one tab to the other?

lol. Lucky I'm not a wrathful Access god, otherwise I would've rained 40 bits and 40 bytes of wrath upon your puny database and drowned the poor unsuspecting integers.

Actually I think your database is larger than any of the 2.5 databases I've made. One of mines might be larger but only because it is a random collection of half-completed projects that I did for fun. That's right, I made a "database" for fun. But to show you how disconnected it is, one of its many functions is a cookbook and another function is a sudoku maker.

Anyways, getting back from that tangent.
1) You must mean form instead of table. Yes, you're going to have to make that a subform because it needs to have the foreign key populated.

2) Definitely, I did this in one of my recent databases. Tabs are there to help spread out the information. I had a large data collection form that I spread out over 4 tab pages with a button at the bottom to let them move between tabs, even though they could click the tab they wanted to go to. But people who enter data don't like to use the mouse. This form also had 3 subforms one of which was all by its lonesome of a tab page because it was so large.

[AccessIdiot]

lol. Lucky I'm not a wrathful Access god, otherwise I would've rained 40 bits and 40 bytes of wrath upon your puny database and drowned the poor unsuspecting integers.

Actually I think your database is larger than any of the 2.5 databases I've made. One of mines might be larger but only because it is a random collection of half-completed projects that I did for fun. That's right, I made a "database" for fun. But to show you how disconnected it is, one of its many functions is a cookbook and another function is a sudoku maker.

That is so awesome.

Anyways, getting back from that tangent.
1) You must mean form instead of table. Yes, you're going to have to make that a subform because it needs to have the foreign key populated.

Yes I did mean form, sorry about that. Fixed that too.

2) Definitely, I did this in one of my recent databases. Tabs are there to help spread out the information. I had a large data collection form that I spread out over 4 tab pages with a button at the bottom to let them move between tabs, even though they could click the tab they wanted to go to. But people who enter data don't like to use the mouse. This form also had 3 subforms one of which was all by its lonesome of a tab page because it was so large.

Thanks for that. I got the subform moved to another tab, now I have to figure out how to use the button to bring the other tab forward. Is it possible to lock a tab until a button is clicked? I think I will post this in a new thread though, in case it could help someone else.

Cheers Rabbit and all others that helped me with this!

:) melissa

[Denburt]

Your quite welcome Melissa and thanks Rabbit for taking up the slack nice job.

As a note I am a keyboard fanatic, I hate using the mouse unless it is needed. On my tab control I make sure the caption is in place and like most controls you simply place the & sysmbol before the letter or page number and your rolling with a keyboard shortcut. Page&1 etc.

[Rabbit]

That is so awesome.



Yes I did mean form, sorry about that. Fixed that too.



Thanks for that. I got the subform moved to another tab, now I have to figure out how to use the button to bring the other tab forward. Is it possible to lock a tab until a button is clicked? I think I will post this in a new thread though, in case it could help someone else.

Cheers Rabbit and all others that helped me with this!

:) melissa

Not a problem, good luck.

[NeoPa]

Thanks for that. I got the subform moved to another tab, now I have to figure out how to use the button to bring the other tab forward. Is it possible to lock a tab until a button is clicked? I think I will post this in a new thread though, in case it could help someone else.
:) melissa

Nice idea to put up a new thread Melissa.
Can you post a link in here to the new one - just for ease of finding it.

Cheers -Adrian.

PS. I too am impressed by the help that the boys have managed to offer. Well done Denburt & Rabbit :)

[AccessIdiot]

Your quite welcome Melissa and thanks Rabbit for taking up the slack nice job.

As a note I am a keyboard fanatic, I hate using the mouse unless it is needed. On my tab control I make sure the caption is in place and like most controls you simply place the & sysmbol before the letter or page number and your rolling with a keyboard shortcut. Page&1 etc.

I'm a keyboard fanatic too and I"ve noticed that you can tab through the controls on the form but they have to be in the same order as the fields in the table. That's kind of a bummer because I've arranged the fields in the tables to make the relationships diagram as clean as possible (I hate overlapping lines) but that doesn't correspond well with how I have set up my forms. Oh well.

Can you explain the keyboard shortcut a little more? (well, I supposed I could research it. :) ). If your tab control is called Page&1 what key do you hit on the keyboard to go to that tab? Is it just "1"?

thanks!

Oh and I will post a link to any new thread as soon as I create one. :)

[Rabbit]

I'm a keyboard fanatic too and I"ve noticed that you can tab through the controls on the form but they have to be in the same order as the fields in the table. That's kind of a bummer because I've arranged the fields in the tables to make the relationships diagram as clean as possible (I hate overlapping lines) but that doesn't correspond well with how I have set up my forms. Oh well.

Can you explain the keyboard shortcut a little more? (well, I supposed I could research it. :) ). If your tab control is called Page&1 what key do you hit on the keyboard to go to that tab? Is it just "1"?

thanks!

Oh and I will post a link to any new thread as soon as I create one. :)

If you right click the form and select tab order you can change how the tabbing flows.

When you use Page&1 as the caption, it shows up as Page1 which means that when the form is open in normal view, you can hit Alt+1 and it will jump to that tab. So with something like &Specimen, it will show up as Specimen and hitting Alt+s will jump to that tab.

[Denburt]

Rabbit is right about the tab order but the form has to be in design view to see this, also on each control there is a property that you can change, so you can change each control individually.
Another note you do want to take into consideration that there are menu's that have keyboard shortcuts etc. and you may want to avoid double dipping so to speak. Your users may not understand that if they hold the alt key then hit that repeated shortcut twice will usually get them where they want.

[NeoPa]

If you right click the form and select tab order you can change how the tabbing flows.

You can also rearrange the Tab Order of controls within a form by setting the Tab Index property explicitly. Watch out though, as every time you set it this way, it will automatically re-arrange the other controls to fill up the numbers from 0 to the number of controls -1. Easiest to manage the Tab order in the way Rabbit describes.

When you use Page&1 as the caption, it shows up as Page1 which means that when the form is open in normal view, you can hit Alt+1 and it will jump to that tab. So with something like &Specimen, it will show up as Specimen and hitting Alt+s will jump to that tab.

This is a fundamental concept that is used throughout Microsoft software. for instance, in Excel you can open the Edit menu simply by hitting Alt-e. When used fully this can be very powerful.

:Edit:
Denburt's post nipped in under mine and contains some more useful advice so please don't miss it because I posted over the top.

[AccessIdiot]

If you right click the form and select tab order you can change how the tabbing flows.

When you use Page&1 as the caption, it shows up as Page1 which means that when the form is open in normal view, you can hit Alt+1 and it will jump to that tab. So with something like &Specimen, it will show up as Specimen and hitting Alt+s will jump to that tab.

Brilliant, this will be very useful thanks.

Good advice from all (don't worry NeoPa, I read EVERYTHING, sometimes 2 and 3 times!) and while I would, in a heartbeat, implement keyboard shortcuts for myself I really need to "dumb it down" for the db users, the folks that will be entering in the data - either in the field themselves or in the office through their hired $8/hr techs (c'mon, we've all been there). So I need to fool proof this thing as much as I can in the time allowed. So I've figured out how to disable scrolling with the mouse wheel (which goes through records) and how to remove navigation controls etc but I need to figure out how to use a custom made button to go back and forth between the tabs. That thread is here

cheers :)

[AccessIdiot]

If you right click the form and select tab order you can change how the tabbing flows.

I can't believe I never found this. Bloody brilliant - thanks again.

[NeoPa]

Good advice from all (don't worry NeoPa, I read EVERYTHING, sometimes 2 and 3 times!)

I think you probably do Melissa :)
It's a good practice and well worth the trouble.
BTW Thanks for posting the link.