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 :-)

[Denburt]

A little tired (feeling brain dead) this should work but I know there is a better way.

if me.dirty then
MyBookMark = Me.Bookmark
Me.Requery
Me.Bookmark = MyBookMark
end if

[AccessIdiot]

Denburt to my rescue again. :-)

Okay I think I know that dirty is when you have data on a form that hasn't been entered yet right? But what is bookmark?

[Denburt]

It is what it says it is lol. ;) A bookmark merely marks your position so that after you requery the form you can return to your current location. If you have 6 records but your editing record 2 then after you requery thats where you will return the second record. :) You can use that in your recordsets and such or maybe synchronize between a recordsetclone and your form (maybe you searched the recordset found your record now want to display it).

me.bookmark = rs.bookmark

[Denburt]

Im not sure if you know this so I thought I would toss it out there. If you highlight a word in VBA and press F1 you will get the help file on that word (if it exists in the helpfile). I don't mind answering your questions though, I know it is usually easier to understand plain english than some of the helpfiles.

[AccessIdiot]

Okay I'm sure that works great but maybe that isn't what is going on with my project. :-) Somehow I knew it couldn't be that simple.

Basically I have an autonumber field called Replicate_ID that lives in the Replicate table. It is tied in a one to many relationship to the Specimen table. Basically one replicate can have many specimens. So on the Replicate form (subform actually, that you helped me with enabling) there is a button for "add specimen information". Click on the button and it opens the Specimen table. When I start typing info into the Specimen form (which feeds the Specimen table). I expect to see the Replicate ID field populate but it doesn't. The control source is set to tbl_replicate.r eplicateID (as opposed to tbl_specimen.re plicateID).

Since the two are linked in a one to many it should auto populate right? That's why I suspected it wasn't getting into the replicate table.

Any clue what I'm doing wrong?

And yeah, I knew about F1 but I at first thought bookmark referred to a fieldname or control name on one of your projects. :-) That'll show me. And yeah, the help is sometimes no help and at best somewhat confusing. I like real world discussion and examples I find on this forum.

[Denburt]

O.K. If I understand this then, ReplicateID will not auto populate in the specimen table right? Well its not supposed to it does in the Replicate Table because it is Autonumber but in the Specimen table you still need to choose 1 (or set a default value) and you can't add one to the replicate table from the specimen table (if thats the problem we may want to look at your relationships).

And yeah, I knew about F1 but I at first thought bookmark referred to a fieldname or control name on one of your projects. :-)

FYI you will or shouldn't EVER see me use a regular word for a field. I try and maintain a strict policy about that. The problem is that if I named a field BookMark and then tried to use it, well it could get nasty ya know. I have watched so many people use Date for a field name as an example... Date() is a function to retrieve todays date. I will usually use PurchDate or something to that effect It is also more relevant since I am using that for a purchase date. just something to keep in mind.

[AccessIdiot]

No, sorry, I don't mean autoincrement. I meant that since they are tied together in a one to many relationship I would just expect to see the value from one form appear on the other form.

Isnt' that how it works when two fields are tied together? If you set Customers.Custo merID = 5 on a form, and then while in that record you open up the Orders form, then shouldn't Orders.Customer ID = 5? Kind of like a subform within a form?

I also tried sending the value via OpenArgs but that failed too.

I'm sure it's something fundamental that I'm just not seeing and will smack my forehead when its explained to me.

[Denburt]

Yeah your right take a look at your main form in design view (I think I got ya) click the control that is holding your subfom not the form itself then view the properties. You should see:

Link Child Fields
Link Master Fields

Place your Replicate_ID in both places Child Field would be the subform master well you get it.

Is that the issue at hand?

[AccessIdiot]

Yeah your right take a look at your main form in design view (I think I got ya) click the control that is holding your subfom not the form itself then view the properties. You should see:

Link Child Fields
Link Master Fields

Place your Replicate_ID in both places Child Field would be the subform master well you get it.

Is that the issue at hand?

lol kind of. I was using the form/subform analogy to point out how I think it should work. It works great on a form to subform level but how to make it work from one form to another? (or from a subform to a completely different form).

The button is on the subform, it launches a completely new and different form, and I want the primary key of the table that the subform feeds to appear on the new form (and get entered into the table underlaying the new form).

To use the same analogy as above, if the Customers form was actually a subform but there was a button to launch the Orders form I would want the CustomerID on the Customers subform to show up on the Orders form and get entered into the Orders table (where Customers.Custo merID = Orders.Customer ID).

Am I talking the same thing you are and just not getting it? I tend to do that a lot.

*edit* oh and do you have to have the field physically on the subform/form for it to work? I mean, the ID field gets populated even if you don't see it simply by being a new record. I'm just trying to associate my Specimen(s) with a particular ReplicateID.

[Denburt]

Gotcha when you open the new form I presume you are using Docmd.openform?
If so add your ID to the openargs like so Docmd.OpenForm ,,,,,,me!Replic ate_ID
Then in the form that pops up go into the onOpen event and you can add the following.

    If not IsNull(Me.OpenArgs) Then
         me!Replicate_ID = Me.OpenArgs
    End if

You may need the fields to be on the forms if so just set visible to no and your good.
:)

[AccessIdiot]

Hey there,

I'm getting an error message: "You can't assign a value to this object" for that code. :-(

Any idea why?

[AccessIdiot]

Nope, wait, it does work but only if I leave the recipient textbox unbound. I need to get the value transferred through OpenArgs into the 2nd table.

In other words, I have ReplicateID transferred over to the form but I need to get it into the Specimen table (where Replicate.Repli cateID = Specimen.Replic ateID).

[AccessIdiot]

Still can't figure this one out. How do I assign the value passed by OpenArgs to the value in the table?

Thank for any help!

[Denburt]

Try to update it manually can you do that?