make sure data is entered before going to new form?

[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.R eplicate_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.Repli cateID = Specimen.Replic ateID).

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

Specimen.Replic ateID 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.Replic ate_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.Repli cate_ID.
sbfrm_Replicate has button that opens frm_Specimen
frm_Specimen has Specimen.Replic ate_ID that should contain the value of Replicate.Repli cate_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.DoMenuIte m 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.R eplicate_ID'"

[Denburt]

Yes, exactly. Except that Specimen.Replic ate_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_Replicat eID = Me.OpenArgs.

But if I have a textbox that is bound to tbl_Replicate.R eplicate_ID or tbl_Specimen.Re plicate_ID on my form I do not see the number. tbl_Replicate.R eplicate_ID is an autonumber PK. tbl_Specimen.Re plicate_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.Re plicate_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.Re plicate_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.Re plicate_ID

[Denburt]

The control source is tbl_Specimen.Re plicate_ID

And is the control name:
Replicate_ID
If so then

Me!Replicate_ID = me.openargs

Should work...