complex form operations?

[AccessIdiot]

Please forgive as I am an Access and VB newbie.

For what it's worth, here is my complete setup. Maybe it will help or maybe it will just confuse but I'll outline it anyway.

I have a main form that has 3 buttons. Each button opens the same form (frm_Survey) but passes a different string to the form. When a button is clicked the main form is closed to prevent the user from pressing another button. The form that is opened is frm_Survey. The string that is passed from the button via OpenArgs is concatenated with an integer that the user enters in an unbound textbox on frm_Survey and that value is put in the table (tbl_Survey) and also populates a disabled field (txt_SuveyNumID ).

So for example, the user hits the "Trawl" on the main form. The main form closes, frm_Survey opens, and 'TR' is passed to frm_Survey. On frm_Survey the user types in "2" and in txt_SurveyNumID you would see "TR2" which also gets entered into SurveyNum field of tbl_SurveyNum.

There is a button on frm_Survey that launches frm_Replicate. The concatenated field from frm_Survey (txt_SurveyNumI D) should also show up on frm_Replicate (getting passed by OpenArgs) and be entered into the table that feeds frm_Replicate (tbl_Replicate - they are linked by a one to many relationship: one survey can have many replicates).

There is a button on frm_Replicate that adds a new record (New Replicate). The user should be able to add as many records as they like, keeping that concatenated field that was passed from frm_Survey (txt_SurveyNumI D). When they are done, they should be able to click on a New Survey button that closes frm_Replicate and return to frm_Survey. Now the user can change the number in the integer field, which is concatenated with the string passed from the main form, click a button to open frm_Replicate again, and start over with the new concatenated value.

In other words, "New Survey" button closes frm_Replicate. Type "3" into the field on frm_Survey and you should now see "TR3". Hit "New Replicate" and frm_Replicate opens with "TR3" as Survey_Num.

I hope this isn't too confusing. If it would help to draw a picture I can do that if you tell me how to upload attachments.

Now here are my problems:

1) When I hit "New Replicate" button on frm_Replicate it doesn't advance to a new record. I get an error message: "Can't go to specified record". This is the code on the button:

Private Sub btnNewReplicate_Click()
On Error GoTo Err_btnNewReplicate_Click
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnNewReplicate_Click:
    Exit Sub
 
Err_btnNewReplicate_Click:
    MsgBox Err.Description
    Resume Exit_btnNewReplicate_Click
 
End Sub

2) When I click on "New Survey" frm_Replicate closes like it should, but I need the data that is currently on the frm_Survey to get entered into the table and then advance to a new record so I can change the Survey_Num. How do I do this? Right now if I change the integer and go to a New Replicate then whatever was last in the field is what is getting entered. In other words, if first it is TR2 and then I go back and change it to TR3 then only TR3 is going into the database. I think I need some kind of Requery somewhere? Maybe on the button on frm_Replicate that goes back to frm_Survey (New Survey)?

I have all sorts of code snippets that I can provide as well if that would help.

Thanks in advance and sorry for the long post!!

cheers,
melissa

[Rabbit]

Rather than a new form, could I suggest a continuous subform on frm_survey? If you link the master and child fields the foreign key will transfer automatically.

[AccessIdiot]

Thanks for your response, I'm trying this now.

Cheers!

[Rabbit]

Good Luck, let us know how you get along. If you get this working correctly, you can hide the field on the subform altogether so they won't be able to change it.

Also, if you set up the relationship to cascade updates and cascade deletes, then whenever they delete or change the primary key value for a record, it updates to the linked table. And if they delete a primary key value, it deletes all records in the linked tables with the same foreign key value.

[NeoPa]

I would certainly support that advice (about the cascading updates/deletes) Melissa.
Can you let us know if this question is still current. From your other thread I feel this may have been superceded?

[AccessIdiot]

I've got things working with a subform and form. I'd link to learn more about cascading as you suggested but I'm going to post in the other thread I started about locking/disabling as it is more pertinent there.

Thanks for your help!

[NeoPa]

Can you post a link in here to your other thread then, so I can check it out.

[Rabbit]

The thread is here.

[NeoPa]

Thanks Rabbit. Top man.