Need help with For Each Next syntax 1

[hthagood]

I am trying to create a set of records in tblPayments based on the the records in qryBudget. The two tables are related by cost code. I am creating a command button on a form (based on the billing period of the payments) to run this expression. I want to create the records using the "billingperiodno" from the form and the "cost code" from the query.

This is what I have come up with so far:

For Each CostCode In qryBudget
DoCmd.RunSQL "INSERT INTO tblPayments (CostCode,ActualCost,ProjectedCost, BillingPeriodNo, Contract) VALUES ('" & qryBudget.CostCode & "', 0, 0,Me.BillingPeriodNo,'N/A');"
Next CostCode


But this gives me a type mismatch and expected variable errors. I think this is the right track, but my syntax is all wrong. I would appreciate any help.

 

[MacroScope]

I just made the same suggestion to someone else. Why not create a query that returns what you want to see and then copy the SQL code if you don't want to use the query itself? Personally, I think the query is a helluva lot easier than the SQL and it's effectively the same thing. The query generates the SQL, and executes it, but it gives you all the visual clues you need without fumbling through the code itself.

 

[Gammachaser]

Try this

DoCmd.RunSQL "INSERT INTO tblPayments (CostCode, ActualCost, ProjectedCost, BillingPeriodNo, Contract) VALUES ('" & qryBudget.CostCode & "', 0, 0,[b]" & Me.BillingPeriodNo & "[/b],'N/A');"

If Me.BillingPeriodNo is text (it is poorly named if it is) add single quotes where appropriate.

 

[dhookom]

I would try to do this with one SQL and no looping through qryBudget:

Dim strSQL as String
strSQL = "INSERT INTO tblPayments " & _
  "(CostCode,ActualCost,ProjectedCost, BillingPeriodNo, Contract) " & _
  "SELECT DISTINCT CostCode , 0, 0, " & Me.BillingPeriodNo & ",'N/A' FROM qryBudget" 
Currentdb.Execute strSQL , dbFailOnError

This also assume BillingPeriodNo is numeric.

Duane
Hook'D on Access
MS Access MVP

 

[hthagood]

I think I need to refer to the Me.BillingPeriodNo differently.

Me.BillingPeriodNo is on the master form 'frmBudgetControl' and the data is in the subform 'frmPayments'. I get a compile error stating "method or data member not found

 

[Gammachaser]

The plot thickens. That would have been good information to know before we started to offer suggestions.

Try Me.Parent.BillingPeriodNo. That may work unless there are other surprises for us.

And dhookom is right. Once again I concentrated on the obvious error and failed to see the bigger picture.

 

[hthagood]

That got rid of the compile error, but I now get a runtime 2465 "application-defined or object-defined error"

I guess the only other "surprise" is that I have all my forms on an unbound, tabbed "frmMain" Don't konw if that's relevant, but I thought I would give you the whole picture.

 

[Gammachaser]

Everyone knows that TMI means 'Too Much Information' (actually to those of us in the Nuclear Power Industry, it means something different.) What we have here is the opposite. NEI? Not Enough Information?

Where do you get the error? What values are in your variables at the time? What is highlighted when the code breaks? What does the surrounding code look like?

As I have read here before, Help us help you.

 

[hthagood]

I get the error when I click the command button. I don't know how to see what the variable value is. The entire strSQL is highlighted. I don't have any other code surronding the form, other than form navigation buttons.

 

[Gammachaser]

Please show us exactly what your SQL code is that fails.

 

[hthagood]

Well, I had a spelling error in my form in the "billingperiodno" field, so that's where the error was...it works perfectly now.

My only issue now is getting it to requery correctly; I started a separate post on it, but I am still struggling. I can't seem to get it right.

Thanks for the help on this...much appreciated!

 

[kjv1611]

hthagood,

If someone's post here helped you fix your issue for this thread (not the requery part), then it'd be helpful to everyone else for you to point out which user's post was helpful. You can do that by clicking on the link, "Thank so and so for this valuable post!