mainform opens subform in add mode, but one field is already filled

[Hutchy]

Hey guys, I need some assistance, please.

I'm using Access 2007.

I have a few problems:

1. I have a switchboard. I want to click a button, that opens a form with a dropdown list, when I make my selection, it opens a subform in add mode, but the linked field in the subform isn't empty, but filled with the mainform's field value that I selected?

OR

2. Is there a way for me to open a form in add mode, add data to it, click the add button (I will add an add button) that allows me to add again, but this time a particular field is not empty, but filled with selected info from previous selection?
Say for instance, I have 2 fields (both combo box fields), I click add, made selections for both fields, I click add again, but this time one of the fields stays constant like it's already been selected. It's filled with what was selected from before.

Either of those 2 - which ever is simplier. Thanks.

 

[shoji]

Hutchy,

I am not sure if I understand your situation, but I will give you my answer to your Option 2.

The scenario, as I understand it, goes like:

On the main form, you have a button "Add" and when you click on that, a new form will pop up with a new record except that the two fields are filled with the words that were already selected on the main form.

You will need some codes, but I will give you a step-by-step instruction here.

1. Create a button "Add" on the main form. Let's call this control "cmdAdd".
2. On the Property Sheet for this button, go to On Click and select "Event Procedure".
3. Write for cmdAdd_Click event:

Private Sub cmdAdd_Click()
Dim strOpenArgs As String

    strOpenArgs = Nz(ComboBox1) & "\" & Nz(ComboBox2)
    DoCmd.OpenForm "frmAddForm", , , , , acDialog, strOpenArgs

End Sub

In the above codes, strOpenArgs is a string that sends to the pop-up form the data from the ComboBoxes that are presumably already selected.
In the next line, "frmAddForm" is the name of the form you want to pop up for the data entry. Make sure there are 5 commas, followed by "acDialog". "acDialog" opens the form in such a way that you cannot do anything until you close this form. The last parameter is strOpenArgs. You get this value when you open the pop up form.

4. Create a form you want to open for data entry. Set the record source for this form.
5. On the form's Property Sheet, go to "On Load" and write:

Private Sub Form_Load()
Dim strOpenArgs As String
Dim S As Long

    strOpenArgs = Me.OpenArgs
    S = Instr(1, strOpenArgs, "\")
    Field1 = Left(strOpenArgs, S-1)
    Field2 = Mid(strOpenArgs, S+1)

    DoCmd.GoToRecord , , acNewRec

End Sub

Field1 and Field2 are the ones you want to fill in with the main form's combobox data. When this pop up form opens, they are already filled with the appropriate data.

Basically this will do. However, if I were you, I would add a precautionary clause to block opening the pop up form if those two comboboxes are not selected yet. In that case, the first codes would be:

Private Sub cmdAdd_Click()
Dim strOpenArgs As String

    If Nz(ComboBox1) = "" Or Nz(ComboBox2) = "" Then
      MsgBox "Select the combos first, please."
      Exit Sub
    End If

    strOpenArgs = Nz(ComboBox1) & "\" & Nz(ComboBox2)
    DoCmd.OpenForm "frmAddForm", , , , , acDialog, strOpenArgs

End Sub

I hope this will answer your question.

Shoji

 

[Hutchy]

...

Thanks for responding Shoji, I will test and let you know


But just to clarify, that your solution is to what I'm requesting assistance for:

Form 1:
combo box with [projectname (to bake a cake, to climb a tree, to design a logo)] info (projectname being the fieldname)

Form 2:
combo box [projectname]
combo box [employeename]
combo box [activity]
combo box [sub_activity]
button (add)
.
.
.
Form 1
combo box [to climb a tree] (select to a climb a tree)

Form 2: (form 2, subform, opens with to climb a tree already selected)
combo box [to climb a tree]
combo box [blank]
combo box [blank]
combo box [blank]
button (add)
.
.
.
I fill in the rest of form 2, then I click add and it should give me a blank form 2 so I can add another record, but combo box [to climb a tree] is already selected for the new add as well, and so on...

 

[shoji]

Hmm.. I am somewhat lost. In my example, Form 2 is not a sub form. It is a pop-up form. When you finish Form 2, you close it. Now you are back to Form 1. Then you click "Add" button and the same thing will happen.
Anyway, why don't you test my example first?

 

[Hutchy]

Hmm.. I am somewhat lost. In my example, Form 2 is not a sub form. It is a pop-up form. When you finish Form 2, you close it. Now you are back to Form 1. Then you click "Add" button and the same thing will happen.
Anyway, why don't you test my example first?

No. Never mind, it works!