Adding record in Access form 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

 

[dhookom]

I would add some code that would set the default value of the MemberID to the value in the filter. You can grab the value from the Filter property, Form 1, any existing record, or send it in the OpenArgs of DoCmd.OpenForm.

Duane
Hook'D on Access
MS Access MVP

 

[bearatc]

Thank you for your response. Where would I find an example of the proper syntax that would assign the value from the filter to the MemberID? I see the value of the filter listed in the property sheet but I don't know how to set that as the default value for new records.

 

[dhookom]

It might be easier to set the default value to the control on the form 1.

To find the value of the filter, add a text box with a control source of:
=Filter

Then you can figure out what the value is and write your code to pull the value to the right of the =.


Duane
Hook'D on Access
MS Access MVP

 

[bearatc]

I added the text box with the control source set to "=[Filter]", but when I run the form it doesn't just place the value in the text box, it places everything that was listed in the Property sheet for the form- "[MemberID]=8457126". How do I get it to just put the filter value in the text box? If I could get it to do that, I can certainly do the rest. Thank you again for your attention and patience.

 

[dhookom]

As you can now see, the appropriate default value would be to the right of "[MemberID]=". You can use the Mid() function along with the Instr() function to extract just the number. Use the extracted number to set the default value of MemberID in the newly opened form.

Duane
Hook'D on Access
MS Access MVP

 

[bearatc]

I got it now, thanks for your help.