Adding new records with filtered data

[bearatc]

I have an Access form that I am having trouble assigning a specific value to a field in that form. I have mostly used the wizards to create a lot of my forms, so I only have minimal experience with VBA coding. I will try to give the clearest picture on what I’m trying to do.
From “Form1” I added a button to open “Form2”. By using the wizards to create the “Form2”, when “Form2” opens, it has filtered the records based on the field “MemberID”. “Form1” is based on a “Table1” that has the field “MemberID” in it and “Form2” is based on “Table2” and it also has a field “MemberID” in it, so it can be used to link the two tables.
Once “Form2” opens from “Form1”, it displays the filtered records as it should. I want to add records to “Form2”. I created a button from the wizards to add a new record, but I want the button to also fill in the “MemberID” on “Form2” that is the same as the “MemberID” that was used to filter the data from “Form1”. Here is the only code that is on the Button on “Form2”. What code do I need to add so that the “MemberID” from “Form1” is placed in the “MemberID” field in the new record on “Form2”?

Private Sub AddBut_Click()
On Error GoTo Err_AddBut_Click

DoCmd.GoToRecord , , acNewRec


Exit_AddBut_Click:
Exit Sub

Err_AddBut_Click:
MsgBox Err.Description
Resume Exit_AddBut_Click

End Sub

 

[PNGBill]

On Form2 Open in Design View and set the Data Property - Data Entry No, Allow Additions Yes.

 

[PNGBill]

I have re read your post.

You have one button to open Form2 and a 2nd button to add new records to form2 or just one button ?

 

[missinglinq]

Since all Records on Form2 have the same MemberID, simply assign the current one to a Variable, then after the move to the New Record, assign it back:

varID = Me.MemberID
DoCmd.GoToRecord , , acNewRec
Me.MemberID = varID

Linq ;0)>

 

[bearatc]

Thank you, Missinglinq. That worked great for the ones that already had a record associated with them in that Table2/Form2, but there are some Members that don't yet have a record in that Table2. I know I can run an Append query and put a record in for each Member, but is there another way you would recommend for those particular Members that don't have an associated record in Form2?

 

[bearatc]

PNGBill,

There is one button on Form1 that opens Form2 filtered by MemberID. On Form2 I added an Add Record Button, that I wanted it to add a record and populated the MemberID field with the data from the filter, since it is also the MemberID from Form1.

 

[PNGBill]

See attached sample database (2010 accdb)
One button can either view existing records or allow a new record to be added - to a sub form.

 

[bearatc]

Thank you. I will look at the sample, but my form2 is not a subform of the Form1.

 

[PNGBill]

Thank you. I will look at the sample, but my form2 is not a subform of the Form1.

True. Put the command button on form2 and shorten the code which no longer needs to refer to a control on a subform.

 

[missinglinq]

This routine opens a second Form and finds a matching Record, or if none exists, opens the second Form to a New Record and fills in the MemberID.

In first form

Private Sub Go2FormB_Click()
If Not IsNull(Me.MemberID) Then
  DoCmd.OpenForm "TableB", , , , , , Me.MemberID
 Else
  MsgBox "A Visit ID Must Be Entered First!"
 End If
End Sub

In the second form

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
 Set rst = Me.RecordsetClone
 
 rst.FindFirst "[MemberID] = '" & Me.OpenArgs & "'"
 'rst.FindFirst "[MemberID] = " & Me.OpenArgs ' Use this for a Numeric ID
  If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.MemberID = Me.OpenArgs
   End If

rst.Close
Set rst = Nothing
End If

End Sub

Linq ;0)>

 

[bearatc]

Thanks again Missinglinq. I must be doing something wrong because I can get that to work. Going off of your tag line "There's ALWAYS more than one way to skin a cat", I can't help but look at this another way. I tried to create a text box in Form2 with the control source "=[Filter]", thinking that if I could get the filter value into a new text box, I could then just add the code "textbox = Me.MemberID" after the addnewrecord command .

The problem is that when I did that, the textbox didn't contain just the number value. It returned with the entire filter expression from the form property sheet, which was "[MemberID]=458124". Is there a way to get just the numerical value from the filter into the new text box field?

 

[bearatc]

I figured it out. Thank you so much for your help.