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.replicateID (as opposed to tbl_specimen.replicateID).

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.CustomerID = 5 on a form, and then while in that record you open up the Orders form, then shouldn't Orders.CustomerID = 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.CustomerID = Orders.CustomerID).

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!Replicate_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.ReplicateID = Specimen.ReplicateID).

[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?

[AccessIdiot]

What do you mean? :-)

[Denburt]

On your form see if you can enter the information from the first form (the info you want carried to the second form) into the field using the keyboard.

[AccessIdiot]

Well, I'm hoping to make the field invisible, so I need it to be filled in automatically.

Essentially, I just need to tell Access that when I click on the button to start adding Specimen information that they are all tied to that one Replicate. So no matter how many Specimens I create with the Specimen form, the same Replicate ID is going into the Specimen table.

[Denburt]

Well, I'm hoping to make the field invisible, so I need it to be filled in automatically.

Essentially, I just need to tell Access that when I click on the button to start adding Specimen information that they are all tied to that one Replicate. So no matter how many Specimens I create with the Specimen form, the same Replicate ID is going into the Specimen table.

I understand however for troubleshooting purposes... Make it visible and see if it is updateable (humor me) :) If not then check the underlying recordsource and see if it is updateable in there.

[AccessIdiot]

I'm sorry, I'm a little lost here. If I pass the value using OpenArgs in the DoCmd.OpenForm then it passes successfully to an unbound textbox.

Private Sub Form_Open(Cancel As Integer)               'Pass replicate id when form opens
If Not IsNull(Me.OpenArgs) Then
         Me.txt_ReplicateID = Me.OpenArgs
    End If
End Sub

But it doesn't get into the database because it is an unbound textbox.

If I add a textbox that has a control source of tbl_Replicate.Replicate_ID then I cannot type into it because it is an autonumber field.

[Denburt]

Sounds like our wires are getting crossed a bit here. Lets backup,

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

Replicate.ReplicateID sounds as though it should be the Primary Key set to autonumber.

Specimen.ReplicateID should be a Forien Key and set as a number field.

When you open the Specimen form you pass the argument in and the two tables will have a common key.

[AccessIdiot]

Yes, exactly. Except that Specimen.Replicate_ID is not getting updated and I think it's because when the form is opened the Replicate info hasn't been officially entered into it's own table, so there is no ID to pass. Does that make sense?

Essentially:
sbfrm_Replicate has Replicate.Replicate_ID.
sbfrm_Replicate has button that opens frm_Specimen
frm_Specimen has Specimen.Replicate_ID that should contain the value of Replicate.Replicate_ID but nothing is showing up.

My guess is that the data on sbfrm_Replicate hasn't gotten into the table yet so nothing is being passed.

How do I remedy this?

[Denburt]

The following is really derived from the old DoCmd.DoMenuItem command but more efficient.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
 

:)

[AccessIdiot]

Can you explain what this does and how it is used? Does it replace current code? Do I still use the OpenArgs?

thanks!

[AccessIdiot]

So I put those two lines of code into the button code that launches the form, ahead of the DoCmd.Open command and am still getting the same error.

I checked the table though and the replicate info is going into the replicate table, just not into the specimen table.

FYI the error message I am getting is
"The Microsoft Jet database engine cannot find a record in the table 'tbl_Replicate' with key matching field(s) 'tbl_Specimen.Replicate_ID'"

[Denburt]

Yes, exactly. Except that Specimen.Replicate_ID is not getting updated and I think it's because when the form is opened the Replicate info hasn't been officially entered into it's own table, so there is no ID to pass. Does that make sense?

No it doesn't because earlier in the thread you stated that is would work for an unbound control and so it should work for a bound control, unless it isn't updatable such as an autonumber field, however the value would still be stored in the openargs we can test the openargs to see if it has a value using debug (see below code).

Can you explain what this does and how it is used? Does it replace current code? Do I still use the OpenArgs?

thanks!

Add this to your code before you open the new form (using your open args). This will make sure the reecord in question is saved to the table before you move on to the next form.

Try this goto the VBA window press the menu "view" then "Immediate Window" (Ctrl G for shortcut fanatics). Now do you see the window that has a title of immediate? If so then good now your code will look something like this:

Private Sub Button1_Click()
 
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
 
'The following line will produce any result in the immediate window for your _ review after you test the form come back here and look in the immediate _ window for your value.
 
Debug.print  Me!Replicate_ID
 
Docmd.OpenForm,,,,,,Me!Replicate_ID
 
End sub
 

How are we doing now?

The RunCommand essentially replaces the code in the first page of this post (I mentioned me being tired should have said exhausted) my appologies it is effective but nasty.

Code to remove:

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

Proper method:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
 

[AccessIdiot]

No it doesn't because earlier in the thread you stated that is would work for an unbound control and so it should work for a bound control, unless it isn't updatable such as an autonumber field, however the value would still be stored in the openargs we can test the openargs to see if it has a value using debug (see below code).

Okay in the immediate window I see the number. I know it is getting passed because it does show up in the unbound textbox when I assign me.txt_ReplicateID = Me.OpenArgs.

But if I have a textbox that is bound to tbl_Replicate.Replicate_ID or tbl_Specimen.Replicate_ID on my form I do not see the number. tbl_Replicate.Replicate_ID is an autonumber PK. tbl_Specimen.Replicate_ID is a number field FK and they are tied in a one to many where one replicate can have many specimens but one specimen belongs to only one replicate.

Do I have the relationships wrong?

[Denburt]

Backing up a couple of posts lets try it manually, In your tbl_Specimen Form can you enter records including the tbl_Specimen.Replicate_ID field? If it is a number field you should be able to manually type in the number from the previous form. If you can then everything should work, if not then we need to look at your recordsource for that form.

[AccessIdiot]

Backing up a couple of posts lets try it manually, In your tbl_Specimen Form can you enter records including the tbl_Specimen.Replicate_ID field? If it is a number field you should be able to manually type in the number from the previous form. If you can then everything should work, if not then we need to look at your recordsource for that form.

Yes I can manually enter in the value. It barks if I enter in the wrong value. It only goes into the db if I manually enter the value. The control source is tbl_Specimen.Replicate_ID

[Denburt]

The control source is tbl_Specimen.Replicate_ID

And is the control name:
Replicate_ID
If so then

Me!Replicate_ID = me.openargs

Should work...

[AccessIdiot]

I have that on form load and I get an error message that I can't assign a value to the object. :-(

[Denburt]

Sorry I wasn't more clear that would go into the open event.

Private Sub Form_Open(Cancel As Integer)
If not IsNull(Me.OpenArgs) Then
         me!Replicate_ID = Me.OpenArgs
End if
 

[AccessIdiot]

lol, it doesn't like that one either.

[Denburt]

It has to be something simple that is being overlooked. If you can add a value to that field manually then there is no reason you can't do it the way we have it in code.

[AccessIdiot]

I agree but I am at a loss as to where to look! Any ideas on what would cause the error? Is there something that is blocking the assignment that I could look into? Could it be conflicting with some other event?

[NeoPa]

It has to be something simple that is being overlooked. If you can add a value to that field manually then there is no reason you can't do it the way we have it in code.

Yes there is. It's a timing issue.
I had a (v v) long discussion with Mary the other day about this sort of thing and it will not be allowed at that stage (OnOpen) due to the form not yet having a current record (I think). Clearly an unbound control will not have the same restrictions.

Melissa,
Can I ask how we got from using subforms - where we were a couple of weeks ago (Separate thread and including sending me a copy of the db) - to using separate forms for this? As discussed, it is much harder to implement (than doing the whole process using subforms). Not impossible, as there are some quite complex work-arounds, but possibly a bridge too far for your current stage of understanding.
BTW Master & Child linked fields (that make the whole process work so smoothly for subforms), are not available for separately called forms.

If we need to, we can get you through this but we will need to be very disciplined in our communication as we are talking some fairly convoluted concepts here and if we get out of step between ourselves then we will all just get confused (Notice the difficulty of communication within this very thread).

[NeoPa]

In short, you need to save the current record of your existing form first before opening the new one passing the OpenArgs.

DoCmd.RunCommand acCmdSaveRecord

In the OnOpen event procedure you need to store the OpenArgs value.
In the OnCurrent you could do something like setting the bound control to the passed value then immediately setting the Dirty flag to False.
Check that your bound controls are properly set up though. They should not be set explicitly to a data source as the data source for all the bound controls on the form should be the datasource that the form itself is bound to.

[AccessIdiot]

In short, you need to save the current record of your existing form first before opening the new one passing the OpenArgs.

DoCmd.RunCommand acCmdSaveRecord

In the OnOpen event procedure you need to store the OpenArgs value.
In the OnCurrent you could do something like setting the bound control to the passed value then immediately setting the Dirty flag to False.
Check that your bound controls are properly set up though. They should not be set explicitly to a data source as the data source for all the bound controls on the form should be the datasource that the form itself is bound to.

Hey NeoPa thanks for stepping in. It makes sense that timing could be an issue, I've seen the same thing in Flash.

You asked about forms and subforms - I did do that successfully but now I am onto a different table/form. To create another nested subform inside the current subform would take up too much screen space. Would it work to use tabs? I could see that happening if working with forms is too difficult.

My project has multiple forms, some are used and some are not. Here is how it is currently setup:

1) User is presented with a main menu form with buttons. User clicks a button and is taken to

2) a survey form. When they click a button on the survey form it enables

3) a replicate subform within the survey form. On the replicate subform they can click a button to enter data about specimens that were caught during the replicate. Clicking the button takes them to

4) the specimen form. If the specimen they have caught is not in the species drop down combo box they can type it in and the NotInList function will

5) open a species form.

So you see there are lots of steps and a variety of forms. Every survey will have a replicate. Not every replicate will have specimen data so it has to be optional not required. But since there can be more than one specimen per replicate the replicate id has to get into the specimen table.

I can sort of follow your suggestions on code but am a little lost on the dirty flag and not really sure what you mean by bound controls not being set to a datasource. I have some controls bound to a table such as Staff that feeds the dropdown. Then the choice is stored in the foreign key in the table that feeds the form. For example the combo box is sourced by Staff.StaffID but the choice made by the user is stored in Replicate.StaffID.

I hope this is something I can resolve soon. The guys I"m building this database for are chomping at the bit and I told them I'd hopefully have the problem fixed by the end of the week! So please let me know if there is another way I should be approaching this. Should I maybe have "stacked" subforms that become visible when needed? Would that be easier to code?

[NeoPa]

Great response Melissa.
Will have to give this some thought.
Can you send me your latest version in the mean-time. This will help me when I'm trying to understand all you've said. It should fill in the context for me. I can forward it on to denburt too, if he expresses a wish to receive it.

[AccessIdiot]

Much appreciated! Will compact and send along momentarily.

[Denburt]

I'll be in and out but would be glad to look at it. Anything I can do to help. :)

[AccessIdiot]

I really REALLY appreciate it. I have sent it to NeoPa and am happy to send it to you if you pm me your email address.

cheers!

[NeoPa]

Much appreciated! Will compact and send along momentarily.

Marvellous!
I won't get home for a while mind you, but it's good to know it'll be there before I am :D

[NeoPa]

I'll be in and out but would be glad to look at it. Anything I can do to help. :)

Unless I hear to the contrary, I'll forward it straight on to you as soon as I get in, and on my PC tonight Denburt.

[NeoPa]

Database forwarded :)

[AccessIdiot]

Cheers :-)

[NeoPa]

OK.
How do we proceed from here? Generally we need to consider that setting the value of a bound control will immediately set the .Dirty flag, which is undesirable. Consider showing the value in an unbound field and setting (a possibly invisible) bound control on update of the form's record.
Can you tell us which forms particularly you're looking to work on at the moment, as there are a number to choose from and, although we want a general purpose solution, we will need to work with specifics to get it working and test it.

As a side-issue. You should tell the people who are impatiently chasing you for this database that, in our (my) opinion, the project undertaken is a fairly lengthy one, even for an experienced developer. For a relative newbie, their expectations are entirely unreasonable. You can quote me on that. I think you're actually doing a very good job in the circumstances and I'd employ someone with your attitude and capability. Given a little space in which to mature, I expect you'll make a more than capable database developer in time.

[AccessIdiot]

OK.
How do we proceed from here? Generally we need to consider that setting the value of a bound control will immediately set the .Dirty flag, which is undesirable. Consider showing the value in an unbound field and setting (a possibly invisible) bound control on update of the form's record.
Can you tell us which forms particularly you're looking to work on at the moment, as there are a number to choose from and, although we want a general purpose solution, we will need to work with specifics to get it working and test it.

As a side-issue. You should tell the people who are impatiently chasing you for this database that, in our (my) opinion, the project undertaken is a fairly lengthy one, even for an experienced developer. For a relative newbie, their expectations are entirely unreasonable. You can quote me on that. I think you're actually doing a very good job in the circumstances and I'd employ someone with your attitude and capability. Given a little space in which to mature, I expect you'll make a more than capable database developer in time.

People are chasing me? You'd hire me? what? lol - I think you have me confused with someone capable.

Is it really that difficult? If so I don't want to tackle this problem and wish for a work around. Where is the work around fairy when you need her? Please wave your magic wand!

Okay. So really the only thing I'd like to make happen here is to link the Replicate and Specimen tables. Right now there is a Replicate_ID in tbl_Specimen_Replicate as a foreign key (this also holds true for Entrainment_ID in tbl_Specimen_Entrainment; whatever we get to work for Replicate_ID I'm sure I can apply to Entrainment_ID).

Because you can have multiple Specimens for one Replicate, any time the Specimen form is launched (from the Replicate subform) it needs to know what Replicate the user was on so that it can store the correct Replicate_ID in the Specimen table.

Does that make sense?

I like the idea of bringing the value into an unbound textbox (already done using OpenArgs and works great) and setting the value to a bound control after form update (have tried this multiple ways and can't get it to work so I must be doing something wrong).

To keep my busy I've started playing with a copy of the database using tabs. I was wondering if it would be better/easier to have in the Replicate subform two tabs: one for replicate information and one for specimen information. Do you think this is a good idea? I know nothing about tabs and so am unsure if when you have tabs you have to fill out info on both to create a record or not.

thanks for any and all ideas.

[NeoPa]

People are chasing me? You'd hire me? what? lol - I think you have me confused with someone capable.

I'm referring to your last paragraph in post #38. Maybe you just meant they're waiting in eager anticipation.
You'd be surprised at how many otherwise intelligent people don't have the 'can do' attitude you show - and the understanding of the importance of understanding what you're doing. I'm forever fixing fixes which are bodged in by others to avoid getting to the seat of the problem.

Is it really that difficult? If so I don't want to tackle this problem and wish for a work around. Where is the work around fairy when you need her? Please wave your magic wand!

I actually doubt you'll get too stuck on this. We'll see.

Okay. So really the only thing I'd like to make happen here is to link the Replicate and Specimen tables. Right now there is a Replicate_ID in tbl_Specimen_Replicate as a foreign key (this also holds true for Entrainment_ID in tbl_Specimen_Entrainment; whatever we get to work for Replicate_ID I'm sure I can apply to Entrainment_ID).

Because you can have multiple Specimens for one Replicate, any time the Specimen form is launched (from the Replicate subform) it needs to know what Replicate the user was on so that it can store the correct Replicate_ID in the Specimen table.

Does that make sense?

Sort of.
We need to be clear what we're referring to though. Are you talking about forms or tables? They are not so closely connected that mixing them up is easily understood. I thought we were working on forms.

I like the idea of bringing the value into an unbound textbox (already done using OpenArgs and works great) and setting the value to a bound control after form update (have tried this multiple ways and can't get it to work so I must be doing something wrong).

Tell me where is the code you've tried in the db sent to me and I'll look in more detail.

To keep myself busy I've started playing with a copy of the database using tabs. I was wondering if it would be better/easier to have in the Replicate subform two tabs: one for replicate information and one for specimen information. Do you think this is a good idea? I know nothing about tabs and so am unsure if when you have tabs you have to fill out info on both to create a record or not.

thanks for any and all ideas.

I can't help much here I'm afraid. If truth be told this is all quite new to me too. I'm just trying to work my way through it with you. It's a good learning experience for me too.

[AccessIdiot]

Tell me where is the code you've tried in the db sent to me and I'll look in more detail.

Yep I agree it's easy to get forms and tables mixed up so I'm going to be extra careful here.

If you open in design view frm_Survey you'll see in the lower half sbfrm_Replicate (I guess you could just as easily open sbfrm_Replicate). In the bottom left corner is a text field. The text field is called Replicate_ID and the control source is tbl_Replicate.Replicate_ID. This is the primary key of tbl_Replicate. You will also notice a button that says "Add Specimen Information". This has an OnClick event associated with it that launches frm_Specimen. There is some additional code on it that was suggested through this thread such as DoCmd.RunCommand and Debug.Print so I'm not sure if that is still needed or not. The OpenForm command has the Replicate_ID passed as OpenArgs.

Now open frm_Specimen_Replicate in design view. On the right hand side below the combo boxes is an unbound textbox that has the name txt_ReplicateID. On the form itself there is an OnOpen event that passes the OpenArgs value to the textbox. I was playing around yesterday and messed up the code. Right now it reads

Private Sub Form_Open(Cancel As Integer)                                        'Pass replicate id when form opens
If Not IsNull(Me.OpenArgs) Then
         Me.ReplicateID = Me.OpenArgs
End If
End Sub

Please change that to Me.txt_ReplicateID and it will pass the value to the unbound textbox. Below the unbound textbox is another textbox called ReplicateID. I was playing around with the Control Source of this box and right now I believe it is set to tbl_Specimen_Replicate.Replicate_ID.

When in sbfrm_Replicate I need whatever Replicate_ID the user is currently on to be passed to frm_Specimen when they click on the "Add Specimen Information" button. The two Replicate_IDs are linked in a one to many relationship. Not just passed to the form but entered into the tables that underlie the forms so that the data in tbl_Specimen_Replicate is linked to tbl_Replicate by the Replicate_ID.

I hope this makes sense!

Help me Obi Wan NeoPa! You're my only hope!

:-)