Change value in a table using VBA

[hollyylloh]

I am using a Dlookup function to return a record from a table and then I
would like to set the value of a field in that record. I have:

Dim acctype as variant

acctype = Dlookup("[AccType]", "tblPermit", "[User] = Forms!frmSignin!user
and [Password] = Forms!frmSignin!password")

If acctype = "Writer" then
docmd.OpenForm "frmMain"
Forms!frmMain!cmdCompany.Enabled = True
' need help here to write a value to the table ?? Tables!tblPermit!Loggedin
= True
' for the user referenced in the Dlookup ??

End if

Perhaps me approach is all wrong? Should I be referencing a recordset?
Thanks for your help!

 

[Jim Burke in Novi]

One way to do it:

dim SQLText as string

SQLText = "UPDATE tblPermit " & _
"SET LoggedIn = True " & _
"WHERE User = '" & Forms!frmSignin!user & "' AND " & _
" Password = '" & Forms!frmSignin!password & "'"
docmd.runsql SQLText

put that right before you open the main form.

Notice that you need to put quotes around the User and Password values, and
you cannot put the form control names inside the quotes - you need to change
your DLookup to do that as well. And make sure you include blanks where I
have them at the end of lines (e.g. at the end of "UPDATE tblPermit "). Also,
you should include error handling in your routine.

 

[hollyylloh]

Jim, thanks. I did get that to work.
One thing though, is there a way to suppress the dialog which comes up and
asks if I want to confirm the update of one row?

Thanks

One way to do it:

dim SQLText as string

SQLText = "UPDATE tblPermit " & _
"SET LoggedIn = True " & _
"WHERE User = '" & Forms!frmSignin!user & "' AND " & _
" Password = '" & Forms!frmSignin!password & "'"
docmd.runsql SQLText

put that right before you open the main form.

Notice that you need to put quotes around the User and Password values, and
you cannot put the form control names inside the quotes - you need to change
your DLookup to do that as well. And make sure you include blanks where I
have them at the end of lines (e.g. at the end of "UPDATE tblPermit "). Also,
you should include error handling in your routine.

I am using a Dlookup function to return a record from a table and then I
would like to set the value of a field in that record. I have:

Dim acctype as variant

acctype = Dlookup("[AccType]", "tblPermit", "[User] = Forms!frmSignin!user
and [Password] = Forms!frmSignin!password")

If acctype = "Writer" then
docmd.OpenForm "frmMain"
Forms!frmMain!cmdCompany.Enabled = True
' need help here to write a value to the table ?? Tables!tblPermit!Loggedin
= True
' for the user referenced in the Dlookup ??

End if

Perhaps me approach is all wrong? Should I be referencing a recordset?
Thanks for your help!

 

[pietlinden]

Jim, thanks. I did get that to work.
One thing though, is there a way to suppress the dialog which comes up and
asks if I want to confirm the update of one row?

Thanks

Two ways. Either:

DoCmd.SetWarnings False 'turn them off
DoCmd.RunSQL SQLText
DoCmd.SetWarnings True ' turn them back on

OR

DBEngine(0)(0).Execute SQLText

 

[Tom van Stiphout]

On Thu, 5 Feb 2009 16:00:01 -0800, hollyylloh

Wrap the code in:
DoCmd.SetWarnings False
'do your thing
DoCmd.SetWarnings True

-Tom.
Microsoft Access MVP

Jim, thanks. I did get that to work.
One thing though, is there a way to suppress the dialog which comes up and
asks if I want to confirm the update of one row?

Thanks

One way to do it:

dim SQLText as string

SQLText = "UPDATE tblPermit " & _
"SET LoggedIn = True " & _
"WHERE User = '" & Forms!frmSignin!user & "' AND " & _
" Password = '" & Forms!frmSignin!password & "'"
docmd.runsql SQLText

put that right before you open the main form.

Notice that you need to put quotes around the User and Password values, and
you cannot put the form control names inside the quotes - you need to change
your DLookup to do that as well. And make sure you include blanks where I
have them at the end of lines (e.g. at the end of "UPDATE tblPermit "). Also,
you should include error handling in your routine.

I am using a Dlookup function to return a record from a table and then I
would like to set the value of a field in that record. I have:

Dim acctype as variant

acctype = Dlookup("[AccType]", "tblPermit", "[User] = Forms!frmSignin!user
and [Password] = Forms!frmSignin!password")

If acctype = "Writer" then
docmd.OpenForm "frmMain"
Forms!frmMain!cmdCompany.Enabled = True
' need help here to write a value to the table ?? Tables!tblPermit!Loggedin
= True
' for the user referenced in the Dlookup ??

End if

Perhaps me approach is all wrong? Should I be referencing a recordset?
Thanks for your help!