Adding a variable value from code to a table field (1 Viewer)

[Deleted member 162737]

Greetings to everyone,

I'm new in VBA (also new member here) and I suppose that someone could help with this matter.

I have Table "Example" and Fields "ID", "A", "B" and "C":

A - Long Text - <Name>
B - Number - <Maximum number>
C - Number - <Current number>

Very simple Code in Module is:

Sub Example()

Dim MN As Integer
Dim CN As Integer
Dim I As Integer

CN = DLookup ("C", "Example", "ID=1")
MN = DLookup ("B", "Example", "ID=1")

For I = 1 To 3
TB = TB + 1
If TB > MB Then
MsgBox " STOP"
Else
MsgBox " Current number is " & CN
End If
CurrentDb.Execute "UPDATE Example SET C = CN WHERE ID = 1"
Next I

End Sub

QUESTION:
How can save data CN (current number) into into field C in table Example?
Thank you all in advance!

 

[MajP]

Not sure if the logic makes any sense or for sure the table makes any sense but
"UPDATE Example SET C = " & CN & " WHERE ID = 1"
that strings resolves to something like
"UPDATE Example SET C = 2 WHERE ID = 1"

Dim x as string
x = "dog"
y = "cat"
debug.print "I walked my pet " & X & "."
debug.print "I walked my pet " & Y & "."
I walked my pet dog
I walked my pet cat

 

[Gasman]

Right.
No Option Explicit. So you can declare anything incorrectly.
TB is not declared, as a consequence of above.

Put the sql into a string variable. Then you can debug.print it until you get it correct, then use that in the command

 

[MajP]

I understand this is an example so not sure what you are really trying. But likely this can be done purely in a query without looping or code.

 

[theDBguy]

Hi. Welcome to AWF!

Can you please explain the purpose of this module using plain words? Thank you.

 

[Deleted member 162737]

I have an input form and two text boxes. In the first enter username, in the second one the password. There is a table in which the values for username and password are already defined. After clicking the button, it is determined whether the input is correct or not. If it is correct, enter the next form ad so on. If it is not, it needs to be entered again. The maximum number of entries is intended to be defined in the table. At each wrong entry, the current value is incremented by 1 and replaced with the previous value in table. At the end when the maximum number of registrations is reached, further logging in is stopped.

 

[Deleted member 162737]

Not sure if the logic makes any sense or for sure the table makes any sense but
"UPDATE Example SET C = " & CN & " WHERE ID = 1"
that strings resolves to something like
"UPDATE Example SET C = 2 WHERE ID = 1"

Dim x as string
x = "dog"
y = "cat"
debug.print "I walked my pet " & X & "."
debug.print "I walked my pet " & Y & "."
I walked my pet dog
I walked my pet cat

I don't understand what I'm doing wrong...

 

[Deleted member 162737]

Right.
No Option Explicit. So you can declare anything incorrectly.
TB is not declared, as a consequence of above.

Put the sql into a string variable. Then you can debug.print it until you get it correct, then use that in the command

Hi Gasman,

you're right. It was a typo.
The code is attached but it still doesn't work...

 

[MajP]

Currentdb.execute

 

[Gasman]

Hi Gasman,

you're right. It was a typo.
The code is attached but it still doesn't work...

No

You put the sql string into a string variable and then use that.
@MajP has given you the syntax

Using that

Dim strSQL As String
...
..
strSQL = "UPDATE Example SET C = " & CN & " WHERE ID = 1"
Debug.Print strSQL
CurrentDb.Execute strSQL