Update from Form (by addition)

[Guest]

I'm not sure why I'm having difficulty with this and I'm sure I'm missing
something simple. We gather statistics from different locations using a
paper form and at the end of the day or the next we take all of the forms and
tally them together. For awhile this was done in excel. I converted it to a
database to improve functionality and report gathering. What I'd like to do
and have partially done, is create a form that looks similar to the paper one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked appropriately
through queries and relationships and can update the various records from one
form. The trouble is that I have to do the addition prior to inputting.
Because the paper forms come in at different times, and sometimes they only
arrive after a week, I have to take the numbers already in the database and
add them to the numbers already in the table then input the number (replacing
the old) into the form. It seems like I should be able to add a function
somewhere to the field property in the form to do this calculation for me.
I'd like to avoid making a query where it asks me for every number one at a
time which has been the solution I've thought of... it's probably faster for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes at
once and I can rapidly put the numbers into the fields and it takes those
numbers and adds them to numbers already in the table.

 

[Carl Rapson]

I'm not sure why I'm having difficulty with this and I'm sure I'm missing
something simple. We gather statistics from different locations using a
paper form and at the end of the day or the next we take all of the forms
and
tally them together. For awhile this was done in excel. I converted it
to a
database to improve functionality and report gathering. What I'd like to
do
and have partially done, is create a form that looks similar to the paper
one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked appropriately
through queries and relationships and can update the various records from
one
form. The trouble is that I have to do the addition prior to inputting.
Because the paper forms come in at different times, and sometimes they
only
arrive after a week, I have to take the numbers already in the database
and
add them to the numbers already in the table then input the number
(replacing
the old) into the form. It seems like I should be able to add a function
somewhere to the field property in the form to do this calculation for me.
I'd like to avoid making a query where it asks me for every number one at
a
time which has been the solution I've thought of... it's probably faster
for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes at
once and I can rapidly put the numbers into the fields and it takes those
numbers and adds them to numbers already in the table.

Create an unbound form that contains all the text boxes you need and a
command button. Give each textbox control the same name as the field you
want to add its value to. The user enters the values to be added and clicks
the button. In the Click event of the command button, build and execute
UPDATE statements that update your table with the entered values. Something
like:

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE

SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl


Carl Rapson

 

[Guest]

Thank you Carl, I've got this on a test database that's also been simplified.
I'm not sure where to place the names of the fields in the form. IN this
instance I have five fields (one key and four to be updated) from a table
called Dates. Can you tell me if this looks correct?

Private Sub Command8_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Dates] SET [" ctl.Name &"]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl

End Sub

I'm getting a syntax error if I try to change anything in ctl.Name, I feel
like a quote is maybe missing somewhere?
Thanks for you help.
--
-Beeler

I'm not sure why I'm having difficulty with this and I'm sure I'm missing
something simple. We gather statistics from different locations using a
paper form and at the end of the day or the next we take all of the forms
and
tally them together. For awhile this was done in excel. I converted it
to a
database to improve functionality and report gathering. What I'd like to
do
and have partially done, is create a form that looks similar to the paper
one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked appropriately
through queries and relationships and can update the various records from
one
form. The trouble is that I have to do the addition prior to inputting.
Because the paper forms come in at different times, and sometimes they
only
arrive after a week, I have to take the numbers already in the database
and
add them to the numbers already in the table then input the number
(replacing
the old) into the form. It seems like I should be able to add a function
somewhere to the field property in the form to do this calculation for me.
I'd like to avoid making a query where it asks me for every number one at
a
time which has been the solution I've thought of... it's probably faster
for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes at
once and I can rapidly put the numbers into the fields and it takes those
numbers and adds them to numbers already in the table.

Create an unbound form that contains all the text boxes you need and a
command button. Give each textbox control the same name as the field you
want to add its value to. The user enters the values to be added and clicks
the button. In the Click event of the command button, build and execute
UPDATE statements that update your table with the entered values. Something
like:

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE

SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl


Carl Rapson

 

[Carl Rapson]

It looks correct to me. The names of the fields go into the Name properties
of the textboxes. If you have fields in your table named Field1, Field2,
Field3, and Field4, your 4 textbox controls should be named Field1, Field2,
Field3, and Field4 also. Set the name on each control's Properties window,
on the Other tab. You don't need to try to change what's in ctl.Name in your
code, because you're using it to build your SQL UPDATE string.

Carl Rapson

Thank you Carl, I've got this on a test database that's also been
simplified.
I'm not sure where to place the names of the fields in the form. IN this
instance I have five fields (one key and four to be updated) from a table
called Dates. Can you tell me if this looks correct?

Private Sub Command8_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Dates] SET [" ctl.Name &"]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl

End Sub

I'm getting a syntax error if I try to change anything in ctl.Name, I feel
like a quote is maybe missing somewhere?
Thanks for you help.
--
-Beeler

I'm not sure why I'm having difficulty with this and I'm sure I'm
missing
something simple. We gather statistics from different locations using
a
paper form and at the end of the day or the next we take all of the
forms
and
tally them together. For awhile this was done in excel. I converted
it
to a
database to improve functionality and report gathering. What I'd like
to
do
and have partially done, is create a form that looks similar to the
paper
one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked
appropriately
through queries and relationships and can update the various records
from
one
form. The trouble is that I have to do the addition prior to
inputting.
Because the paper forms come in at different times, and sometimes they
only
arrive after a week, I have to take the numbers already in the database
and
add them to the numbers already in the table then input the number
(replacing
the old) into the form. It seems like I should be able to add a
function
somewhere to the field property in the form to do this calculation for
me.
I'd like to avoid making a query where it asks me for every number one
at
a
time which has been the solution I've thought of... it's probably
faster
for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes
at
once and I can rapidly put the numbers into the fields and it takes
those
numbers and adds them to numbers already in the table.

Create an unbound form that contains all the text boxes you need and a
command button. Give each textbox control the same name as the field you
want to add its value to. The user enters the values to be added and
clicks
the button. In the Click event of the command button, build and execute
UPDATE statements that update your table with the entered values.
Something
like:

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE

SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl


Carl Rapson

 

[Guest]

Thanks for the reply Carl, it's still giving me an error. It's telling me
that there is a complie error: Expected: End of Statement. It highlights
ctl. I'm using VB 6.3. I thought I had it worked out once, but I can't seem
to find the correct syntax it's looking for. I have the names and control
sources matched appropriately. Any ideas?


--
-Beeler

It looks correct to me. The names of the fields go into the Name properties
of the textboxes. If you have fields in your table named Field1, Field2,
Field3, and Field4, your 4 textbox controls should be named Field1, Field2,
Field3, and Field4 also. Set the name on each control's Properties window,
on the Other tab. You don't need to try to change what's in ctl.Name in your
code, because you're using it to build your SQL UPDATE string.

Carl Rapson

Thank you Carl, I've got this on a test database that's also been
simplified.
I'm not sure where to place the names of the fields in the form. IN this
instance I have five fields (one key and four to be updated) from a table
called Dates. Can you tell me if this looks correct?

Private Sub Command8_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Dates] SET [" ctl.Name &"]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl

End Sub

I'm getting a syntax error if I try to change anything in ctl.Name, I feel
like a quote is maybe missing somewhere?
Thanks for you help.
--
-Beeler

I'm not sure why I'm having difficulty with this and I'm sure I'm
missing
something simple. We gather statistics from different locations using
a
paper form and at the end of the day or the next we take all of the
forms
and
tally them together. For awhile this was done in excel. I converted
it
to a
database to improve functionality and report gathering. What I'd like
to
do
and have partially done, is create a form that looks similar to the
paper
one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked
appropriately
through queries and relationships and can update the various records
from
one
form. The trouble is that I have to do the addition prior to
inputting.
Because the paper forms come in at different times, and sometimes they
only
arrive after a week, I have to take the numbers already in the database
and
add them to the numbers already in the table then input the number
(replacing
the old) into the form. It seems like I should be able to add a
function
somewhere to the field property in the form to do this calculation for
me.
I'd like to avoid making a query where it asks me for every number one
at
a
time which has been the solution I've thought of... it's probably
faster
for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes
at
once and I can rapidly put the numbers into the fields and it takes
those
numbers and adds them to numbers already in the table.

--
-Beeler

Create an unbound form that contains all the text boxes you need and a
command button. Give each textbox control the same name as the field you
want to add its value to. The user enters the values to be added and
clicks
the button. In the Click event of the command button, build and execute
UPDATE statements that update your table with the entered values.
Something
like:

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE

SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl


Carl Rapson

 

[Jana]

Thanks for the reply Carl, it's still giving me an error. It's telling me
that there is a complie error: Expected: End of Statement. It highlights
ctl. I'm using VB 6.3. I thought I had it worked out once, but I can't seem
to find the correct syntax it's looking for. I have the names and control
sources matched appropriately. Any ideas?

--
-Beeler

It looks correct to me. The names of the fields go into the Name properties
of the textboxes. If you have fields in your table named Field1, Field2,
Field3, and Field4, your 4 textbox controls should be named Field1, Field2,
Field3, and Field4 also. Set the name on each control's Properties window,
on the Other tab. You don't need to try to change what's in ctl.Name in your
code, because you're using it to build your SQL UPDATE string.

Carl Rapson

Thank you Carl, I've got this on a test database that's also been
simplified.
I'm not sure where to place the names of the fields in the form. IN this
instance I have five fields (one key and four to be updated) from a table
called Dates. Can you tell me if this looks correct?
Private Sub Command8_Click()
Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Dates] SET [" ctl.Name &"]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub
I'm getting a syntax error if I try to change anything in ctl.Name, I feel
like a quote is maybe missing somewhere?
Thanks for you help.
--
-Beeler
:
I'm not sure why I'm having difficulty with this and I'm sure I'm
missing
something simple. We gather statistics from different locations using
a
paper form and at the end of the day or the next we take all of the
forms
and
tally them together. For awhile this was done in excel. I converted
it
to a
database to improve functionality and report gathering. What I'd like
to
do
and have partially done, is create a form that looks similar to the
paper
one
and then just throw the numbers into the database from the paper form.
I've got it set up so that all of the tables are linked
appropriately
through queries and relationships and can update the various records
from
one
form. The trouble is that I have to do the addition prior to
inputting.
Because the paper forms come in at different times, and sometimes they
only
arrive after a week, I have to take the numbers already in the database
and
add them to the numbers already in the table then input the number
(replacing
the old) into the form. It seems like I should be able to add a
function
somewhere to the field property in the form to do this calculation for
me.
I'd like to avoid making a query where it asks me for every number one
at
a
time which has been the solution I've thought of... it's probably
faster
for
me to do the calculations by hand.
Any ideas? I'm thinking along the lines of an update query where it
asks you for input, but I'd like one form that displays all input boxes
at
once and I can rapidly put the numbers into the fields and it takes
those
numbers and adds them to numbers already in the table.
--
-Beeler
Create an unbound form that contains all the text boxes you need and a
command button. Give each textbox control the same name as the field you
want to add its value to. The user enters the values to be added and
clicks
the button. In the Click event of the command button, build and execute
UPDATE statements that update your table with the entered values.
Something
like:
Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE

SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
Carl Rapson- Hide quoted text -

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

 

[Guest]

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Steve thanks for your input. I had caught the & actually quite early on. I
think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already
have a combo box set to identify which record I would like to go to and I
have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.
--
-Beeler

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

--
-Beeler

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler

think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

--
-Beeler

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

You didn't post the SQL of the rowsource of Combo11, but it looks like you
have a field "number" (which is a reserved word and shouldn't be used for
object names) as the primary key.

Try changing the "CurrentDb.Execute" line in "Sub Command10_Click" to

CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" & ctl.Name & "]
+ " & ctl.value & " WHERE Number = " & Val(Me.Combo11), dbFailOnError

(should be one line)


I have a question. How is your form laid out? Do you have the button, combo
box and unbound controls in the form header and bound controls in the detail
section? How many unbound controls?

I ask because made a form as described above and I keep getting errors. The
current code for the button doesn't stop with the unbound controls; it keeps
checking all controls on the form. So when it reaches the first textbox in
the detail section and cannot find a matching field in the table, it throws
an error.

Maybe adding a line "on error resume next" might solve this.


For a more "brute force" method, take a look at this:

'----------------------------------------
Private Sub Combo11_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

'disable button until record is found
Me.Command11.Enabled = False
Set rs = Me.RecordsetClone
rs.FindFirst "[numberid] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
'enable button
Me.Command11.Enabled = True
Else
MsgBox "Record not found"
End If
End Sub
'-------------------------------

Private Sub Command11_Click()

'ubField1, ubField2, ubField3, etc. could be named
'anything, even Tom, Dick, Harry

If IsNumeric(Me.ubField1) Then
Me.field1 = Me.field1 + Me.ubField1
End If
If IsNumeric(Me.ubField2) Then
Me.field2 = Me.field2 + Me.ubField2
End If
If IsNumeric(Me.ubField3) Then
Me.field3 = Me.field3 + Me.ubField3
End If

Me.Refresh

'disable button so can't mash it twice
Me.Command11.Enabled = False

'to clear entries
' Me.ubField1
' Me.ubField2
' Me.ubField3

End Sub
'--------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler

think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

--
-Beeler


:

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

I hate it when the computer doesn't do what I am thinking....

The last lines in "Sub Command11_Click()" should be:


'to clear entries
' Me.ubField1 = Null
' Me.ubField2 = Null
' Me.ubField3 = Null



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

You didn't post the SQL of the rowsource of Combo11, but it looks like you
have a field "number" (which is a reserved word and shouldn't be used for
object names) as the primary key.

Try changing the "CurrentDb.Execute" line in "Sub Command10_Click" to

CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" & ctl.Name & "]
+ " & ctl.value & " WHERE Number = " & Val(Me.Combo11), dbFailOnError

(should be one line)


I have a question. How is your form laid out? Do you have the button, combo
box and unbound controls in the form header and bound controls in the detail
section? How many unbound controls?

I ask because made a form as described above and I keep getting errors. The
current code for the button doesn't stop with the unbound controls; it keeps
checking all controls on the form. So when it reaches the first textbox in
the detail section and cannot find a matching field in the table, it throws
an error.

Maybe adding a line "on error resume next" might solve this.


For a more "brute force" method, take a look at this:

'----------------------------------------
Private Sub Combo11_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

'disable button until record is found
Me.Command11.Enabled = False
Set rs = Me.RecordsetClone
rs.FindFirst "[numberid] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
'enable button
Me.Command11.Enabled = True
Else
MsgBox "Record not found"
End If
End Sub
'-------------------------------

Private Sub Command11_Click()

'ubField1, ubField2, ubField3, etc. could be named
'anything, even Tom, Dick, Harry

If IsNumeric(Me.ubField1) Then
Me.field1 = Me.field1 + Me.ubField1
End If
If IsNumeric(Me.ubField2) Then
Me.field2 = Me.field2 + Me.ubField2
End If
If IsNumeric(Me.ubField3) Then
Me.field3 = Me.field3 + Me.ubField3
End If

Me.Refresh

'disable button so can't mash it twice
Me.Command11.Enabled = False

'to clear entries
' Me.ubField1
' Me.ubField2
' Me.ubField3

End Sub
'--------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler

think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






--
-Beeler


:

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Thanks again Steve, we got swamped today, so I didn't get a chance to try any
of your code. But a couple of things, My form is laid out with everything in
the detail section of the form: unbound combo box, four text boxes each
visually underneath the previous one and the command button slightly to the
right. The textboxes became bound in the course of following the earlier
instruction to the correspending column in the table.

Number was something I just threw in quickly to test, but I'll change the
name to Dates (that's what it is in the actualy database but I'll keep the
format as autonumber for ease of use).

I think having the unbound combo box in the detail section should solve the
error you are getting. But, I'll see if I can try it tomorrow.

As far as the SQL string for the combo box... I'm not sure how to produce
that. It's control source is the number column (primary key). And as I'm
sure you saw, I have it set to find records within the form matching the
primary key.

Thanks again for your help, and I'll try to play with the coding either
tonight or tomorrow and post what I came up with.

--
-Beeler

You didn't post the SQL of the rowsource of Combo11, but it looks like you
have a field "number" (which is a reserved word and shouldn't be used for
object names) as the primary key.

Try changing the "CurrentDb.Execute" line in "Sub Command10_Click" to

CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" & ctl.Name & "]
+ " & ctl.value & " WHERE Number = " & Val(Me.Combo11), dbFailOnError

(should be one line)


I have a question. How is your form laid out? Do you have the button, combo
box and unbound controls in the form header and bound controls in the detail
section? How many unbound controls?

I ask because made a form as described above and I keep getting errors. The
current code for the button doesn't stop with the unbound controls; it keeps
checking all controls on the form. So when it reaches the first textbox in
the detail section and cannot find a matching field in the table, it throws
an error.

Maybe adding a line "on error resume next" might solve this.


For a more "brute force" method, take a look at this:

'----------------------------------------
Private Sub Combo11_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

'disable button until record is found
Me.Command11.Enabled = False
Set rs = Me.RecordsetClone
rs.FindFirst "[numberid] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
'enable button
Me.Command11.Enabled = True
Else
MsgBox "Record not found"
End If
End Sub
'-------------------------------

Private Sub Command11_Click()

'ubField1, ubField2, ubField3, etc. could be named
'anything, even Tom, Dick, Harry

If IsNumeric(Me.ubField1) Then
Me.field1 = Me.field1 + Me.ubField1
End If
If IsNumeric(Me.ubField2) Then
Me.field2 = Me.field2 + Me.ubField2
End If
If IsNumeric(Me.ubField3) Then
Me.field3 = Me.field3 + Me.ubField3
End If

Me.Refresh

'disable button so can't mash it twice
Me.Command11.Enabled = False

'to clear entries
' Me.ubField1
' Me.ubField2
' Me.ubField3

End Sub
'--------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler

think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






--
-Beeler


:

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Steve, I've made some of the changes and I think we're getting closer, but a
thought just struck me. I think we're thinking about this the wrong way
based on an earlier post. I think what's happening is that we have a form
that was unbound, but then we bound it so we could locate the record source.
This works except we have to put in all of these exceptions such as disable
the button, nullify the fields, etc. Suppose the form was completely
unbound. So that no matter what was entered nothing happend to the source,
but on event click (the command button) it would bind the four form fields to
the respective record based on the combo box?

I'm wondering if this code would be cleaner:?

on click add each text box to (row source) form
bind the text boxes and input the new number into the table
-essentially it should work like an update query except you add the
values at once that you want to update into the table.

The reason I thought of this is that the way we have the code, if I put a
number into the fields in the form, then select a different record source
from the combo box, that number becomes the new number in the table. So if
my idea above doesn't seem feasible, I need a way to prevent this from
happening as well.

--
-Beeler

I hate it when the computer doesn't do what I am thinking....

The last lines in "Sub Command11_Click()" should be:


'to clear entries
' Me.ubField1 = Null
' Me.ubField2 = Null
' Me.ubField3 = Null



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

You didn't post the SQL of the rowsource of Combo11, but it looks like you
have a field "number" (which is a reserved word and shouldn't be used for
object names) as the primary key.

Try changing the "CurrentDb.Execute" line in "Sub Command10_Click" to

CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" & ctl.Name & "]
+ " & ctl.value & " WHERE Number = " & Val(Me.Combo11), dbFailOnError

(should be one line)


I have a question. How is your form laid out? Do you have the button, combo
box and unbound controls in the form header and bound controls in the detail
section? How many unbound controls?

I ask because made a form as described above and I keep getting errors. The
current code for the button doesn't stop with the unbound controls; it keeps
checking all controls on the form. So when it reaches the first textbox in
the detail section and cannot find a matching field in the table, it throws
an error.

Maybe adding a line "on error resume next" might solve this.


For a more "brute force" method, take a look at this:

'----------------------------------------
Private Sub Combo11_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

'disable button until record is found
Me.Command11.Enabled = False
Set rs = Me.RecordsetClone
rs.FindFirst "[numberid] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
'enable button
Me.Command11.Enabled = True
Else
MsgBox "Record not found"
End If
End Sub
'-------------------------------

Private Sub Command11_Click()

'ubField1, ubField2, ubField3, etc. could be named
'anything, even Tom, Dick, Harry

If IsNumeric(Me.ubField1) Then
Me.field1 = Me.field1 + Me.ubField1
End If
If IsNumeric(Me.ubField2) Then
Me.field2 = Me.field2 + Me.ubField2
End If
If IsNumeric(Me.ubField3) Then
Me.field3 = Me.field3 + Me.ubField3
End If

Me.Refresh

'disable button so can't mash it twice
Me.Command11.Enabled = False

'to clear entries
' Me.ubField1
' Me.ubField2
' Me.ubField3

End Sub
'--------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler


:


think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






--
-Beeler


:

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

Good news, I think it was a mix of both ideas. The text box fields needed to
remain unbound, however the labels of those textboxes should be named the
same thing as the column that needs to be edited. I renamed the combo box
dates and set up the code as follows for button:

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
On Error GoTo Date_Error
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value & " WHERE dates = " & Val(Me.Combo11),
dbFailOnError
End If
End If
Next ctl

Me.testnumber1 = Null
Me.testnumber2 = Null
Me.testnumber3 = Null
Me.testnumber4 = Null

Date_Error:
If Err.number = 94 Then
MsgBox "Please Select a Date"
End If

End Sub
____________________________

Then for the combo box:

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[dates] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Everything is fixed now as it should work - almost. To make it so that the
user can not add new records or navigate, I click the form code and change
AddNewRecord to false and then NavigationButtons to false.

Only thing left is when I try to edit one record then another record
sometimes I get an error that says something like: "The record has been
edited by another user, please re-edit the record." This is a problem
because the record does not need to be re-edited. Any thoughts to stopping
the error?

--
-Beeler

Thanks again Steve, we got swamped today, so I didn't get a chance to try any
of your code. But a couple of things, My form is laid out with everything in
the detail section of the form: unbound combo box, four text boxes each
visually underneath the previous one and the command button slightly to the
right. The textboxes became bound in the course of following the earlier
instruction to the correspending column in the table.

Number was something I just threw in quickly to test, but I'll change the
name to Dates (that's what it is in the actualy database but I'll keep the
format as autonumber for ease of use).

I think having the unbound combo box in the detail section should solve the
error you are getting. But, I'll see if I can try it tomorrow.

As far as the SQL string for the combo box... I'm not sure how to produce
that. It's control source is the number column (primary key). And as I'm
sure you saw, I have it set to find records within the form matching the
primary key.

Thanks again for your help, and I'll try to play with the coding either
tonight or tomorrow and post what I came up with.

--
-Beeler

You didn't post the SQL of the rowsource of Combo11, but it looks like you
have a field "number" (which is a reserved word and shouldn't be used for
object names) as the primary key.

Try changing the "CurrentDb.Execute" line in "Sub Command10_Click" to

CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" & ctl.Name & "]
+ " & ctl.value & " WHERE Number = " & Val(Me.Combo11), dbFailOnError

(should be one line)


I have a question. How is your form laid out? Do you have the button, combo
box and unbound controls in the form header and bound controls in the detail
section? How many unbound controls?

I ask because made a form as described above and I keep getting errors. The
current code for the button doesn't stop with the unbound controls; it keeps
checking all controls on the form. So when it reaches the first textbox in
the detail section and cannot find a matching field in the table, it throws
an error.

Maybe adding a line "on error resume next" might solve this.


For a more "brute force" method, take a look at this:

'----------------------------------------
Private Sub Combo11_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object

'disable button until record is found
Me.Command11.Enabled = False
Set rs = Me.RecordsetClone
rs.FindFirst "[numberid] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
'enable button
Me.Command11.Enabled = True
Else
MsgBox "Record not found"
End If
End Sub
'-------------------------------

Private Sub Command11_Click()

'ubField1, ubField2, ubField3, etc. could be named
'anything, even Tom, Dick, Harry

If IsNumeric(Me.ubField1) Then
Me.field1 = Me.field1 + Me.ubField1
End If
If IsNumeric(Me.ubField2) Then
Me.field2 = Me.field2 + Me.ubField2
End If
If IsNumeric(Me.ubField3) Then
Me.field3 = Me.field3 + Me.ubField3
End If

Me.Refresh

'disable button so can't mash it twice
Me.Command11.Enabled = False

'to clear entries
' Me.ubField1
' Me.ubField2
' Me.ubField3

End Sub
'--------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here is the the code for the combo box and Command Button. I've got it set
up on a 'test' table and form to simplify things for now. The table has 5
columns, the first an autonumber as the primary key. That column is called
number and the following are titled as testnumber1 testnumber2 etc. for
simplicity.

Private Sub Command10_Click()

Dim ctl As Control
Dim strSQL As String
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
CurrentDb.Execute "UPDATE [Test] SET [" & ctl.Name & "]=[" &
ctl.Name & "] + " & ctl.Value, dbFailOnError
End If
End If
Next ctl
End Sub

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[number] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I couldn't even guess how else to apply this to the unique record.


--
-Beeler


:


think the issue was that there were spaces between the " and &. I found that
out about an hour ago and, after a little tweaking, came up with the same
problem you did. Any thoughts to isolating the particular record? I already

You would/could add a WHERE clause to the SQL string, but you need some way
to *uniquely* identify *which* record you want updated.

have a combo box set to identify which record I would like to go to and I

What is the SQL of the combo box row source? Is there a PK?

have eliminated the navigation buttons as well as denied the addition of new
records.

Since this action occurs from a button, I would also like to clear the text
boxes after I open the form, and again after I select the record whose
field's I'd like to update.

I'm guessing you mean the unbound controls that you used to enter the data
that is late.
Add lines to clear the controls between the lines "Next ctl" and "End Sub".

'----SNIP-----
Next ctl
Me.name-of-control-1 = NULL
Me.name-of-control-2 = NULL
'etc
End Sub
'-----------------

Maybe you would also post the tweeked code??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)






--
-Beeler


:

Since I had the code, I played with it and realized that the code works but
it is generic. By that I mean it updates EVERY record in the table with the
results of the addition.

Lets say the first record has 22 in [Field1] and the second record has 44 in
[Field1].
If 10 is entered in the unbound control on the form and you click the
button, the results would be 32 in the first record and 54 in the second
record for [Field1].

You need some way to uniquely identify *which* record you want updated.

Also, note that, as written, the code will only update a record. You cannot
enter the data in the unbound controls, click on the button and have a NEW
record created.


I hope you were trying this on a copy of your MDB or you have a back-up

Just my 2 cents worth

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

If you haven't already found it, you are missing an "&" .

It should be:

CurrentDb.Execute "UPDATE [Dates] SET [" & ctl.Name & .......
^^
missing

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks Jana, I had thought of that. I'm pretty sure it has to do with the
arrangement/existence of one of the following: & " [] symbols. I think this
is mostly due to version of VB. Unfortunately I haven't been able to find
any similar syntax to make a guess at what goes where. I've substituted and
deleted the various things, but so far that's led no where.

--
-Beeler


:

- Show quoted text -

Taking a stab here, but here goes:
The text between CurrentDb.Execute and dbFailOnError should all be on
one line.

 

[Guest]

One other snag. This where I figured it would start to become complicated.
In the actual database (which is a bit more complex) One of the forms I'd
like to apply this particular update idea contains a subform. The subform is
also organized by a combo box which is organized by the Date combo box in the
main form. To put it simply, I have two primary keys in one table, the Date
and a text field in one table.

The code works almost perfectly just plugged in as is, except that if you
put a number in one text box it then fills the rest of the text boxes for
that field in the data regardless of the second primary.

Any thoughts to adding a second where clause for the second primary key?
I'm hoping it's that simple.