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.

 

[shannonp]

Try putting this into a command button:

-------code begins

'I'll assume you used 'Table1' as TABLE and 'myControl' as its index key (of type Integer)

Dim StartNum As Integer
Dim TotalAssigned As Integer

DoCmd.SetWarnings False
[StartNum] = InputBox("Input Starting Number&quot
[TotalAssigned] = InputBox("Input Total Numbers to Assign&quot
While StartNum <= TotalAssigned
DoCmd.RunSQL &quot;INSERT INTO Table1 (myControl) VALUES (&quot; & [StartNum] & &quot&quot;
StartNum = StartNum + 1
Wend
DoCmd.SetWarnings True

-------code ends

Hope its what you wanted

 

[NJW]

THanks for the help. It worked with one problem I wanted to pass data to the records along with the control numbers and I keep getting this &quot;Enter Parameter value&quot; request. Any Help would be appreciated

 

[shannonp]

You wanted to pass data to the records?..can you clarify?...also, when exactly is the 'Enter Parameter value request' occuring?

 

[NJW]

Sorry I meant pass text through to the records. The request is happening after the first record is added and for each record after that.
Here is the code

Private Sub Command0_Click()
Dim StartNum As Integer
Dim TotalAssigned As Integer
Dim LeadTech As String
Dim Branch As String

DoCmd.SetWarnings False
[LeadTech] = InputBox(&quot;Enter in Lead Tech&quot
[Branch] = InputBox(&quot;Input Assigned Branch&quot
[StartNum] = InputBox(&quot;Input Starting Number&quot
[TotalAssigned] = InputBox(&quot;Input Last Number Assigned&quot

While StartNum <= TotalAssigned
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & [StartNum] & &quot;,&quot; & Branch] & &quot;,&quot; & [LeadTech] &quot&quot;
StartNum = StartNum + 1
Wend
DoCmd.SetWarnings True
End Sub

 

[WildHare]

You're getting the prompt for STARTNUM because you have it in brackets in your SQL string:

VALUES (&quot; & [StartNum] & &quot;,&quot; & Branch] & &quot;,&quot; & [LeadTech] &quot&quot;

Access thinks it's a parameter prompt.

This is probably not the way I'd have handled this process, so I'll leave it to someone else to suggest a solution based on your technique.

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

 

[shannonp]

Could it be that you missed a bracket? (in red):

Private Sub Command0_Click()
Dim StartNum As Integer
Dim TotalAssigned As Integer
Dim LeadTech As String
Dim Branch As String

DoCmd.SetWarnings False
[LeadTech] = InputBox(&quot;Enter in Lead Tech&quot
[Branch] = InputBox(&quot;Input Assigned Branch&quot
[StartNum] = InputBox(&quot;Input Starting Number&quot
[TotalAssigned] = InputBox(&quot;Input Last Number Assigned&quot

While StartNum <= TotalAssigned
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & [StartNum] & &quot;,&quot; &
[Branch] & &quot;,&quot; & [LeadTech] &quot&quot;
StartNum = StartNum + 1
Wend
DoCmd.SetWarnings True
End Sub

Unfortunately, I'm still a little baffled about how you want to pass text through. Did you want to pass text through after each number in sequence is appended?, rather than passing the them all into 'MyControl' all at once?? (sorry to sound like a broken record, mustn't be my day today )

 

[shannonp]

Correction on the previous post (missed an & as well)

Private Sub Command0_Click()
Dim StartNum As Integer
Dim TotalAssigned As Integer
Dim LeadTech As String
Dim Branch As String

DoCmd.SetWarnings False
[LeadTech] = InputBox(&quot;Enter in Lead Tech&quot
[Branch] = InputBox(&quot;Input Assigned Branch&quot
[StartNum] = InputBox(&quot;Input Starting Number&quot
[TotalAssigned] = InputBox(&quot;Input Last Number Assigned&quot

While StartNum <= TotalAssigned
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & [StartNum] & &quot;,&quot; & [Branch] & &quot;,&quot; & [LeadTech] & &quot&quot;
StartNum = StartNum + 1
Wend
DoCmd.SetWarnings True
End Sub

Off the subject a little, Wildhare...I've considered you quite a guru on matters like this and I must admit I am a little pretentious about you having an alternative method to this, however, as I regard your opinions highly I would be interested in your solution.

 

[WildHare]

I would probably open a recordset and insert the new records, based on the input box responses, which I would have renamed so they don't match the table fields.

...

with rst
while intStartNum <= intTotalAssigned
do
.addnew
!Branch = txtBranch
!LeadTech = txtLeadTech
!StartNum = intStartNum
IntStartNum = IntStartNum + 1
.Update
Loop
.close
End with

----------------------

or something along those lines. I usually try to shy away from a process that dumps records in like this, because it just seems a bit &quot;un-databasey&quot; to store multiple records which, on the face of it, seem pretty much identical.

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]

I am trying to take what they enter in the input fields and populate a field on one of the records. The problem is that when I enter in alpha char(i.e. &quot;ABC&quot it prompts for the data to be entered in again. Thank you for all the help. I am sorry if I am not clear but I am new to this and might not know how to ask the question right.

 

[WildHare]

I think part of your problem is that in a query, anything surrounded by Square Brackets is interpreted as a criteria prompt, just like in the query builder QBE grid.

And are you populating a field (ONE field) in an EXISITING record, or are you adding FIVE new records - your example shows an INSERT TO command, which ADDS new records, as opposed to UPDATING existing ones. I think we have a terminology communication problem here. Once we've squared that away, we should be able to help you with ease.

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

 

[shannonp]

I'm not entirely what you mean WildHare

These variables have been declared:

Dim StartNum As Integer
Dim TotalAssigned As Integer
Dim LeadTech As String
Dim Branch As String

Irregardless of whether square brackets are added or not it would still be referring to the value stored within the above declared variables.

The 2 examples below when excuted do EXACTLY the same thing (and as you know, refer to the exact same variables)...the only difference is that Example 1 includs 'optional' brackets ([ ]).

Example 1:
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & [StartNum] & &quot;,&quot; & [Branch] & &quot;,&quot; & [LeadTech] & &quot&quot;

Example 2:
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & StartNum & &quot;,&quot; & Branch & &quot;,&quot; & LeadTech & &quot&quot;

 

[WildHare]

Well, then why is NJW being PROMPTED again for a variable? Either he's running his inputbox() function twice, or Access is interpreting a FIELD name within that SQL statement as a prompted criteria box.

And I'm not absolutely certain, but I have a feeling that if you have a field named BRANCH, and then declare a variable named BRANCH, and then include something like values(&quot; & [BRANCH] & ..., the FIELD object will take precedence over the declared variable, and Access will think that you need prompting for the value.

I think that's what is at the root of NWJ's problem, if I read his questions correctly.

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

 

[WildHare]

Follow Up: I just tested my hypothesis, and it appears to be correct. This code:

Public Sub TESTIT()
Dim BRANCH As String ' same as my field name
Dim LEAD As String ' same as my field name

BRANCH = InputBox(&quot;Enter Branch&quot
DoCmd.RunSQL &quot;INSERT INTO Table1 (Branch,Lead) VALUES([BRANCH],[LEAD])&quot;

with a FIELD named BRANCH, will prompt you TWICE - once via the INPUTBOX() function, and once for the FIELD NAME that's in the VALUES() clause. The brackets, per se, don't mean anything - it's the fact that his FIELD name is the same as his variable.




You should NOT, as a general rule, declare variables with the same name as table or field objects. I still think that's the ROOT of his 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]

I have changed my variable names and tried it with and without the brackets I am still getting the same prompt. Could it have to do with the fact that I am entering in text that this is happening. When I enter in numbers I am not getting the prompt. Could this be the problem?

 

[WildHare]

Hmmm. I don't see how. Repost your current code chunk and we'll take a look. I will be teaching this PM so if you don't hear back from me immediately, don't think I've given up hope.


J 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]

Here is the code. Thanks Again

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

DoCmd.SetWarnings False
[StrLeadTech] = InputBox(&quot;Enter in Lead Tech&quot
[StrBranch] = InputBox(&quot;Input Assigned Branch&quot
[IntStartNum] = InputBox(&quot;Input Starting Number&quot
[IntTotalAssigned] = InputBox(&quot;Input Last Number Assigned&quot

While StartNum <= TotalAssigned
DoCmd.RunSQL &quot;INSERT INTO Table1 (MyControl,Branch,Lead)
VALUES (&quot; & [IntStartNum] & &quot;,&quot; & [StrBranch] & &quot;,
&quot; & StrLeadTech & &quot&quot;
StartNum = StartNum + 1
Wend

DoCmd.SetWarnings True
End Sub

 

[WildHare]

Copy this code exactly as you see it.
Click TOOLS / REFERENCES and make sure you have the &quot;Microsoft DAO 3.6 Object Library&quot; checked ON

-------------------------------------------------------
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(&quot;Table1&quot

StrLeadTech = InputBox(&quot;Enter in Lead Tech&quot
StrBranch = InputBox(&quot;Input Assigned Branch&quot
IntStartNum = InputBox(&quot;Input Starting Number&quot
IntTotalAssigned = InputBox(&quot;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
----------------------------------------------
I just ran this and it worked FINE...
BRANCH LEAD startnum
branch 1 leaf tech 1
branch 1 leaf tech 2
branch 1 leaf tech 3
branch 1 leaf tech 4
branch 1 leaf tech 5
branch 1 leaf tech 6
branch 1 leaf tech 7
branch 1 leaf tech 8
branch 1 leaf tech 9
branch 1 leaf tech 10

The only thing you need to change is the name of your TABLE in the OpenRecordset method.

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

 

[WildHare]

.. and don't forget the END SUB just after the END WITH...
I didn't highlight enough before I cut/n/pasted..

I don't know why it keeps prompting you. So rather than beat it to death, I tried it using the technique I first suggested yesterday, opening a recordset.

Seems to work just fine and dandy.

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]

Just One more thing. It runs with no prompts but it does not stop until I get an overflow error. it just keeps adding records until it errors out.