inputbox to update value(s) in the table

[Haz]

I have a unbound form that update the table with vba code by clicking a command button. One field on the table is called location. On the form I have the code field as combo box, once a value selected from the combo box the location text box populated with dlookup function.

How can I have the user change the location with inputbox and criteria to make sure the location is updated in the appropriate record is updated with the user input.

table fields

1. type
2. code
3. location
4. date

the criteria will look like this

if the inputbox has value entered then
update location field where combox = " whatever code is selected" and type =" Beginner"

else
do nothing.

end if

don't update if the input box is null if yes or ok clicked

thanks alot

 

[Galaxiom]

This is one way.

If Not IsNull(Me!inputboxname) Then
     Docmd.SetWarnings False
     DoCmd.RunSQL "UPDATE tablename SET Locationfield = Forms!formname.Form!textboxname WHERE tablename.Location = Forms!formname.Form!comboboxname AND Location.Type = Beginner;"
end if
DoCmd.SetWarnings True

However you should place the last line in the exit section of the procedure (which should also be called from the Error procedure) to ensure warnings are turned back on if there is an error.

Once off they will stay off for everything including saving changes on exit.

 

[Haz]

Thanks alot....

I have done this:

'this under the edit button
dim strLoc as string
strLoc = inputbox("Please Enter New Location", "Location Chagnge!")
 

If Not IsNull(Me!strLoc) Then
     Docmd.SetWarnings False
     DoCmd.RunSQL "UPDATE t1 SET Location = Forms!frm_entry.Form!strLoc WHERE t1.Location = 
     Forms!frm_entry.Form!cbocode = "&[cbocode]&" AND Location.Type = Beginner;"
end if
DoCmd.SetWarnings True

it seems to work fine but not updating the table right.

Is something wrong with the temp storage strLoc?

 

[Galaxiom]

You have strLoc as both a variable and a field name. This will confuse you and all that come after even if Access manages to understand.

Using [cbocode] as both a table field and a form control will definitely cause confusion unless you fully reference them whereever they are used.

......" & [cbocode] & " .........

You would only do this if cbocode was a variable and then it wouldn't be inside []
Fieldnames can go straight into the statement.

Was there a good reason you didn't use a bound form?

 

[Haz]

Sorry I must didn't explain it well. strLoc is not a table field nor a form control. I thought this is just temporary holder to pass the input value to the location field where whatever is selected in the cbocode on the form and the table field type="beginner"

I thought is code will work:

Dim strLoc As String
strLoc = InputBox("Please Enter New Location")

If Not IsNull(strLoc) Then
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE t1 SET Location = strLoc WHERE "t1.code" = "Forms!frm_entry!cbocode" And t1.type= "beginner"

 

[Galaxiom]

By "temporay holder" you would mean VBA variable.
I think this is what you are looking for.

DoCmd.RunSQL "UPDATE t1 SET Location = '" & strLoc & "' WHERE t1.code = Forms!frm_entry!cbocode And t1.type= 'beginner'"

When you use [] around something it means it is an object or field such as a collection (eg [Forms]), a table (eg [t1], a field [type].

Placing quote marks around something means it is a text string.
When you need two levels of quotes use single quotes inside. This used above for 'beginner'.

Notice how the singe quotes are also used around the string variable but separated with one inside each of the set the double quotes.

Using & means concatenate. "this string," & " that string" ends up as "this string, that string".

 

[Haz]

Thanks for your help. Works like a charm and great info.

 

[alex_]

Sorry for refering to an old post (better though than starting a new thread...), but I have the same problem here: although the code "works", it does not update the table. Code:

askpass = InputBox("Enter your new password", "Password change input")
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_loginTable_users SET password = '" & askpass & "' WHERE _
username = forms!LoginForm!usernamecombo"
DoCmd.SetWarnings True

Any ideas?

Regards,

Alex

 

[Galaxiom]

Leave Warnings on and see what it tells you.

Check the bound column of the combo is what you are expecting to be put into the SQL.

Put the SQL into a string and check it before running the command.
Concatenate the combo value to get the full SQL. Assuming username is a string:

strSQL = "UPDATE tbl_loginTable_users SET password = '" & askpass & "'" _
       & " WHERE username ='" & Forms!LoginForm!usernamecombo & "'"
 
Debug.Print strSQL

 

[alex_]

Hi Galaxiom,

thank you for your reply. I followed your advice but unfortunately nothing happend. By that I mean, no error message and no changes in the table. Code:

Dim askpass As String
Dim strSQL As String

askpass = InputBox("Enter your new password", "Password change input")
DoCmd.SetWarnings False
strSQL = "UPDATE tbl_loginTable_users SET password = '" & askpass & "'" _
& " WHERE username ='" & Forms!LoginForm!usernamecombo & "'"
DoCmd.SetWarnings True
Debug.Print strSQL

Thank you in advance for your assistance.

Alex

 

[alex_]

Hi again. Let me recapitulate what I have in terms of code that it does not do what I intent to do:

Dim SQL as string

SQL = " UPDATE tbl_loginTable_users " & _
" SET password = '" & chpass2.Value & "'" & _
" WHERE username = '" & Forms!LoginForm!usernamecombo.Value & "'"
DoCmd.RunSQL SQL
CurrentDb.Execute SQL, dbFailOnError

After numerous efforts I have noticed that the problem is in the WHERE clause (probably bad syntax), although I do not get any error messages. If I omit this line, the code works fine (of course it will update all passwords in the table).

Any help please?

Alex

 

[alex_]

Success

Well, after a number of efforts, the solution was really simple: My combobox was referencing column 0 and not 1 that has the usernames :banghead:. I found it out by simply writing the following code to see what the "original" WHERE clause returned:

dim test as string
test = Forms!LoginForm!usernamecombo.Value
msgbox test

After execution, the message box returned the userID value which is column(0). So, in order to make the code to work I simply added the column(1) property:

Dim SQL as string

SQL = " UPDATE tbl_loginTable_users " & _
" SET password = '" & chpass2.Value & "'" & _
" WHERE username = '" & Forms!LoginForm!usernamecombo.column(1) & "'"
DoCmd.RunSQL SQL
CurrentDb.Execute SQL, dbFailOnError

Now it's OK.

Thanks go also to Galaxion for his intervention.

Alex

PS: Of course one might have saved him/herself from all this trouble by writing the WHERE clause as:

" WHERE userID = '" & Forms!LoginForm!usernamecombo.value & "'"

thus using the userID instead of the username.