Populate Table Between two numbers

[mikerea90]

Hey I have been looking through the forums, but I have not found my specific code.

What I am trying to do is based on a length of time, say 24 weeks, I want a table to be populated with a count of 24 weeks, but this count will start at week 9. So it would count week 9 through week 33. And then also, I am trying to attach a salary to this code in another column. This is my code:


Private Sub Command45_Click()
Dim WkStart As Integer
Dim WkEnd As Integer
Dim GrSalary As Currency
Dim rsReser As DAO.Recordset
On Error GoTo Err_Command45_Click
CurrentDb.Execute "Delete * from TBL_SALARY"
Set rsReser = CurrentDb.OpenRecordset("TBL_SALARY", dbOpenDynaset, dbSeeChanges)
WkStart = "9"
WkEnd = Forms!frm_Main.Text2 + 9
Do While WkStart <= WkEnd
rsReser.AddNew
rsReser("Week") = WkStart
rsReser("Salary") = GrSalary
rsReser.Update
WkStart = DCount(1, WkStart)
GrSalary = Forms!frmMain.Text9
Loop

Exit_Command45_Click:
Exit Sub
Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub


Thanks!!

 

[pbaldy]

Perhaps you can expand on what error you get, or what the code does/doesn't do that's incorrect. I see a number of oddities/errors, including:

WkStart = "9" 'will error because WkStart is a numeric variable
WkStart = DCount(1, WkStart) 'incorrect syntax for a DCount()
rsReser("Salary") = GrSalary 'GrSalary hasn't been set the first time through, always set to the same value thereafter

 

[dcb]

Where are you having problems?

 

[mikerea90]

@pbaldy: How do I make "9" work in that situation? As far as WkStart, I was using DCount because I was not sure how to autopopulate the table with consecutive numbers. And then how would I change GrSalary to work in this instance?

@dcb: I am having problems with the "9" and autopopulating the salary table with both the consecutive numbers and the salary.

 

[pbaldy]

Comments in line:

@pbaldy: How do I make "9" work in that situation? Drop the quotes. As far as WkStart, I was using DCount because I was not sure how to autopopulate the table with consecutive numbers. Perhaps WkStart = WkStart +1. And then how would I change GrSalary to work in this instance? Not sure what value you're trying to get, and don't know your data structure so can't say how to get it. Perhaps a DSum?

 

[mikerea90]

It works! Thanks pbaldy.

 

[dcb]

Tell me why you are trying to base this on a form.... where does the form get its data from?

My thinking is

Select the salary for that week from the underlying table - add to your temp table

pbaldy faster ----- cool

 

[mikerea90]

Is there a way for my populated table start at 1, but only fill in the salary from 10 and on?

 

[pbaldy]

If YourNumber > 9 Then
  'Code to fill the salary
End If

 

[mikerea90]

Works! Thanks again pbaldy!

 

[pbaldy]

No problemo.