How to add to the database from a form 1

[NJW]

I need to add a set of control numbers to a database from a form where the user enters in the beginning number and the total number assigned. I want the form to then take the total number assigned and add number of sequential numbers equal to the total assigned starting with the control number put in. Example: user puts in starting number of 10 and total assigned of 5. The form then assignes 10,11,12,13,14 to the database. I have the control number set up as the index key. I have no idea how to do this and would appreciate some help.

 

[WildHare]

Post the code - we'll take a quick look. Check to make sure your variable names are consistent.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[NJW]

Private Sub Command0_Click()
Dim IntStartNum As Integer
Dim IntTotalAssigned As Integer
Dim StrLeadTech As String
Dim StrBranch As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Table1&quot

StrLeadTech = InputBox("Enter in Lead Tech&quot
StrBranch = InputBox("Input Assigned Branch&quot
IntStartNum = InputBox("Input Starting Number&quot
IntTotalAssigned = InputBox("Input Last Number Assigned&quot

With rst
While IntStartNum <= IntTotalAssigned
Do
.AddNew
!StartNum = IntStartNum
!Branch = StrBranch
!Lead = StrLeadTech
.Update
IntStartNum = IntStartNum + 1
Loop
Wend
.Close
End With
End Sub

 

[WildHare]

Hmmm. That looks OK to me. Can you run this from the Immediate window, with a Debug.print IntStartNum between the .Update and the increment of IntStartNum just to see what the heck is going on? I admit puzzlement here - it looks pretty straightforward to me.

Perhaps a lurker with a less clouded mind will detect the problem...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[NJW]

How do I use the immediate window?

 

[WildHare]

Open your code in the VBA editor window, and click VIEW/Immediate Window. This window can act as a 'trace' window, you can print messages to it with the Debug.Print &quot;xxxx&quot; method, or simply type in it with certain commands. You can also 'run' a procedure by clicking in it with the mouse in the editor pane, and hitting RUN, or pressing F5.

If your proc has some &quot;debug.print xxx&quot; statements sprinkled about in it, you can see what's happening with your routine and why it's continuing to loop around.

Your code looked ok - there's gotta be something else that's gumming up the works somewhere.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[PeteJohnston]

Maybe the DO-loop section should be

With rst
While IntStartNum <= IntTotalAssigned
Do
Do While IntStartNum <= IntTotalAssigned
.AddNew
!StartNum = IntStartNum
!Branch = StrBranch
!Lead = StrLeadTech
.Update
IntStartNum = IntStartNum + 1
Loop
Wend
.Close
End With

Wouldn't the original DO....Loop repeat until told to exit (Exit DO, Goto..) or until the IntStartNum field overflows since there are no conditions on the DO? PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[NJW]

That's it. Thank you for your help. Sorry for the inconvenience. :

 

[WildHare]

Pete, thanks for the solution. Odd, because my code, as copied in to a message about 6 or 7 up, seemed to work fine for me...

Oh well. Long as it works. Sure was a lot of sound and fury there for a while.

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm