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!

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" wrote:

> 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, 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

"Jim Burke in Novi" wrote:

> 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" wrote:
>
> > 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!

On Feb 5, 6:00*pm, hollyylloh <hollyyl...@discussions.microsoft.com>
wrote:
> 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
>
> "Jim Burke in Novi" wrote:
>
> > 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
>

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

On Thu, 5 Feb 2009 16:00:01 -0800, hollyylloh
<(E-Mail Removed)> wrote:

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
>
>"Jim Burke in Novi" wrote:
>
>> 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" wrote:
>>
>> > 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!