UPDATE Table with multiple textboxes

[gator]

I have three unbound textboxes on a form. Each textbox is used to input the
amount of money A,B,C deposited. textbox1 is labeled A, textbox2 labeled
B,....

When the user clicks a button, I need to update the table to add a row for A
then add a new row for B and then C.

Below is the code I have so far for the update, but I can't figure out how
to repeat the update for B & C.

Lastly, the table has a field named 'memo'. I would like to add the textbox
caption to that field on each record. So, for the first record, the deposit
field would be whatever number is in the Textbox labled A and the memo would
be A.

here is the code i have for the update.....

Private Sub Command10_Click()
Dim mycon As New ADODB.Connection
Dim adors As New ADODB.Recordset
Set mycon = CurrentProject.Connection
adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
adors.AddNew
adors!amount = Me.Text0
adors!memo = Me.Label1.Caption
adors.Update
adors.Close
mycon.Close
End Sub

thanks

 

[Mike Painter]

I have three unbound textboxes on a form. Each textbox is used to
input the amount of money A,B,C deposited. textbox1 is labeled A,
textbox2 labeled B,....

When the user clicks a button, I need to update the table to add a
row for A then add a new row for B and then C.

Below is the code I have so far for the update, but I can't figure
out how to repeat the update for B & C.

Lastly, the table has a field named 'memo'. I would like to add the
textbox caption to that field on each record. So, for the first
record, the deposit field would be whatever number is in the Textbox
labled A and the memo would be A.

here is the code i have for the update.....

Private Sub Command10_Click()
Dim mycon As New ADODB.Connection
Dim adors As New ADODB.Recordset Dim i as integer
Set mycon = CurrentProject.Connection
adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic

For i = 1 to 3

adors.AddNew
adors!amount = Me.Text0
adors!memo = Me.Label1.Caption
adors.Update next i
adors.Close
mycon.Close
End Sub

thanks

Even without knowing why you want to do this and what value a table with A,
B, C and some numbers has, this is a bad way to do it.

 

[gator]

why is this such a bad way to do it? What is the harm?

 

[gator]

I have three persons depositing money every day and I have three textboxes on
a form representing where I input each person's deposit amount with each
textbox's label captioned with the person's name to indicate where to put
each person's amount. The code will add records to the table of the amount
and person's name based on the textbox and label.

how does the for next loop pick up textbox2 and then textbox3?

 

[John W. Vinson]

I have three persons depositing money every day

And none of them ever go on vacation, and none of them will ever take another
job, and you'll never under any circumstances have a fourth person?

 

[gator]

I must be missing something obvious.

All I'm trying to do is create a direct user friendly interface to enter
some data where code will update the table. If someone goes on vacation,
then the textbox would be zero. If someone quits, the textbox is ignored or
deleted. If someone joins, a textbox will be added. Is it overkill or what?

 

[John W. Vinson]

I must be missing something obvious.

All I'm trying to do is create a direct user friendly interface to enter
some data where code will update the table. If someone goes on vacation,
then the textbox would be zero. If someone quits, the textbox is ignored or
deleted. If someone joins, a textbox will be added. Is it overkill or what?

Yes. It's overkill. A very simple Continuous Form with a combo box for the
person entering the payment and a textbox to enter it will let you put in the
data with *NO CODE AT ALL*, no extra effort for the user, and no ongoing form
and program maintenance effort on your part.

That said... change your code to

Private Sub Command10_Click()
Dim mycon As New ADODB.Connection
Dim adors As New ADODB.Recordset
Set mycon = CurrentProject.Connection
adors.Open "Deposits", mycon, adOpenKeyset, adLockOptimistic
adors.AddNew
adors!amount = Me.Text0
adors!memo = Me.Label1.Caption
adors.Update
adors.AddNew
adors!amount = Me.Text1
adors!memo = Me.Label2.Caption
adors.Update
adors.AddNew
adors!amount = Me.Text2
adors!memo = Me.Label3.Caption
adors.Update
adors.Close
mycon.Close
End Sub

Since there are only three, I don't see any benefit in looping; just
bruteforce three blocks of code for your there blocks of controls.

You will of course have to change the control names from Text0/1/2 and
Label1/2/3 to the actual names of the controls on the form (which you can see
but I cannot).