Tricky Multiple Append Project

[wptaylor4]

This one is long and detailed, but should be a fun challenge for those with more expertise than I.



The synopsis is that I am trying to create VBA that will run 13 separate append queries each month. These queries will take information from a large table and append them to smaller tables according to a vendor number. As of right now I have a parameter that allows me to insert a 4 digit YYMM number (such as 1305 for May 2013) that will let me update the files. These queries all work and will append correctly.

The problem I ran into is that the VBA (as currently constructed), prompts me to input the YYMM for each query (defeating the purpose of the VBA, which is so that I can enter the date once, and walk away).

My solution was to create a form in which I could enter my 4 digit YYMM once and have all of my append queries to run off of that, allowing my VBA to run all of the queries without the prompts.

Thusfar I have not been able to circumvent the prompt issue and I am wondering if there is anybody here who can get the form to fill in the YYMM without the prompts.

[RayMilhon]

2 questions: 1. What version of Access are you running? 2. In each of the queries how do you have the parameter set?

[wptaylor4]

I am running Access 2007 and I had the query parameters originally set as [what period?] to bring up a prompt to enter the YYMM (which worked). Now they are set as [Forms]![PeriodUpdate]![txtUpdateOnhand]. My form is called Period update and my box is called txtUpdateOnhand. When I click on the append query now, I get a prompt that says "Forms!PeriodUpdate!txtUpdateOnhand" which I thought should have been solved by the form in the first place

[wptaylor4]

Allow me to update the situation. The append queries are running off of a system of other queries that are designed to manipulate the information (again they work with the period prompt). I am starting to narrow down the code for the

Private Sub txtUpdateOnhand_Click()
CurrentDb.Execute "ConsignedVendor1OnhandUpdate", dbFailOnError
CurrentDb.Execute "ConsignedVendor2OnhandUpdate", dbFailOnError
'etc for the 13 queries
End Sub

though I am getting an error on that (3061, which I can debug). I am still just super confused on the use of the textbox to enter my YYMM. It doesn't seem to want to work