Passing a Variable, or Passing a Parameter

[ryguy7272]

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[Klatuu]

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.

 

[ryguy7272]

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
..MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---

 

[ryguy7272]

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.

 

[Klatuu]

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[ryguy7272]

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[Klatuu]

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[ryguy7272]

Oh! It's starting to make sense. Maybe I need to use a Query, rather than a
Table. Right now I have a Table named tblLoginSettings. It has a Field,
which is Data Type AutoNumber and another Field, which is Date Type Text.
That's all. i don't use a Query for this. Should l I use a Query for this?
I only have two Queries in this DB and they are actually related to this
Table. I was hoping to get the value from the ComboBox, pass it to the
Table, named tblLoginSettings, and pass that to one of the two Queries,
depending on what is entered into the Table. Does that make any sense? Is
there a better way of doing this?

Please let me know what to do.


Thanks,
Ryan--

--
RyGuy

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP


:

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[Klatuu]

Can you describe what is it you want to do? I guess I am not clear on the
intent. If you can tell me what, perhaps I can tell you how.
--
Dave Hargis, Microsoft Access MVP

Oh! It's starting to make sense. Maybe I need to use a Query, rather than a
Table. Right now I have a Table named tblLoginSettings. It has a Field,
which is Data Type AutoNumber and another Field, which is Date Type Text.
That's all. i don't use a Query for this. Should l I use a Query for this?
I only have two Queries in this DB and they are actually related to this
Table. I was hoping to get the value from the ComboBox, pass it to the
Table, named tblLoginSettings, and pass that to one of the two Queries,
depending on what is entered into the Table. Does that make any sense? Is
there a better way of doing this?

Please let me know what to do.


Thanks,
Ryan--

--
RyGuy

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy


:

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP


:

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[ryguy7272]

Sure. I’d certainly appreciate any and all help with this! Basically, under
Tools > Startup, I have Display Form/Page, which shows frmLogon. This forces
this particular Form to show when Access starts up. Pretty basic stuff,
right. So now, with the Tables and other things hidden, only that Form
shows. On the frmLogon, I have a ComboBox, named ‘cboEmployee’, which loads
names from Row Source ‘SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY
tblLogin.Login;’. Pretty basic, right. This Form has a TextBox, where a
user can input their password. The Form also has a command button, named
‘cmdLogin’. Based on the user and the password, one of two forms opens, and
will eventually display links certain reports. Anyway, I’m trying to get the
user, from the ComboBox, into a Table, named ‘tblLoginSettings’.
‘tblLoginSettings’ consists of only a FieldName ‘ID’, which is Data Type
AutoNumber, and FieldName ‘Current User’, which is Data Type text. I was
hoping to be able to pass the variable from the ComboBox, named ‘cboEmployee’
to the ‘tblLoginSettings’ in the CurrentUser Field. Let’s say, for instance,
asmith is selected from the ComboBox, how can I transfer that variable to the
Table ‘tblLoginSettings’ and the Field ‘CurrentUser’? I guess it is kind of
complex. I’m not sure Access can do this, but I’ll bet it can…

It seems like you gave me the code to build that string. How do i pass that
value to the 'CurrentUser' Field in the ‘tblLoginSettings' Table? I’d
appreciate any help that you could offer on this.


Regards,
Ryan---


--
RyGuy

Can you describe what is it you want to do? I guess I am not clear on the
intent. If you can tell me what, perhaps I can tell you how.
--
Dave Hargis, Microsoft Access MVP

Oh! It's starting to make sense. Maybe I need to use a Query, rather than a
Table. Right now I have a Table named tblLoginSettings. It has a Field,
which is Data Type AutoNumber and another Field, which is Date Type Text.
That's all. i don't use a Query for this. Should l I use a Query for this?
I only have two Queries in this DB and they are actually related to this
Table. I was hoping to get the value from the ComboBox, pass it to the
Table, named tblLoginSettings, and pass that to one of the two Queries,
depending on what is entered into the Table. Does that make any sense? Is
there a better way of doing this?

Please let me know what to do.


Thanks,
Ryan--

--
RyGuy

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy


:

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP


:

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[ryguy7272]

I just tried this code (similar to something that I found on this DG):
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

strName = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
'CurrentDb.Execute strSQL, dbFailOnError

CurrentDb.Execute "Update [tblLoginSettings], [CurrentUser]='" & strName &
"' Where"
[CurrentUser] = " & strName, dbfailonerror "

End Sub

This was the error:
The expression After Update you entered as the event property setting
produced the following error: Function call on left-hand side of argument
must return a Variant or Object.

What does that mean? I think I am close to fixing the problem here; I
really hope so.

--
RyGuy

Sure. I’d certainly appreciate any and all help with this! Basically, under
Tools > Startup, I have Display Form/Page, which shows frmLogon. This forces
this particular Form to show when Access starts up. Pretty basic stuff,
right. So now, with the Tables and other things hidden, only that Form
shows. On the frmLogon, I have a ComboBox, named ‘cboEmployee’, which loads
names from Row Source ‘SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY
tblLogin.Login;’. Pretty basic, right. This Form has a TextBox, where a
user can input their password. The Form also has a command button, named
‘cmdLogin’. Based on the user and the password, one of two forms opens, and
will eventually display links certain reports. Anyway, I’m trying to get the
user, from the ComboBox, into a Table, named ‘tblLoginSettings’.
‘tblLoginSettings’ consists of only a FieldName ‘ID’, which is Data Type
AutoNumber, and FieldName ‘Current User’, which is Data Type text. I was
hoping to be able to pass the variable from the ComboBox, named ‘cboEmployee’
to the ‘tblLoginSettings’ in the CurrentUser Field. Let’s say, for instance,
asmith is selected from the ComboBox, how can I transfer that variable to the
Table ‘tblLoginSettings’ and the Field ‘CurrentUser’? I guess it is kind of
complex. I’m not sure Access can do this, but I’ll bet it can…

It seems like you gave me the code to build that string. How do i pass that
value to the 'CurrentUser' Field in the ‘tblLoginSettings' Table? I’d
appreciate any help that you could offer on this.


Regards,
Ryan---


--
RyGuy

Can you describe what is it you want to do? I guess I am not clear on the
intent. If you can tell me what, perhaps I can tell you how.
--
Dave Hargis, Microsoft Access MVP

Oh! It's starting to make sense. Maybe I need to use a Query, rather than a
Table. Right now I have a Table named tblLoginSettings. It has a Field,
which is Data Type AutoNumber and another Field, which is Date Type Text.
That's all. i don't use a Query for this. Should l I use a Query for this?
I only have two Queries in this DB and they are actually related to this
Table. I was hoping to get the value from the ComboBox, pass it to the
Table, named tblLoginSettings, and pass that to one of the two Queries,
depending on what is entered into the Table. Does that make any sense? Is
there a better way of doing this?

Please let me know what to do.


Thanks,
Ryan--

--
RyGuy


:

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy


:

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP


:

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---

 

[Klatuu]

I assume in addtion the the user's login name, you also have their password
in tblLogin. So, here is what I would do. I would make the table
tblLoginSettings the form's record source. I would make the combo box's
control source the CurrentUser field. Then change the combo to have two
columns. Make the second column the password. Also change the Column Count
property to 2 and the Column Widths to 2";0" The 2 can be whatever necessary
to show the login name and the 0 will hide the password. Then change the row
source query to:
SELECT DISTINCT Login, Password FROM tblLogin ORDER BY Login;

Get rid of the command button and use the Before Update event of the
password text box:

Private Sub txtPassword_BeforeUpdate(Cancel As Integer)
Static lngRetryCount as Long

If lngRetryCount >= 3 Then
MsgBox "Number of Attemps Exceeded", vbCritical
Docmd.Quit
Else
With Me
If .txtPassword <> .cboEmployee.Column(1) Then
MsgBox "Incorrect Password"
lngRetryCount = lngRetryCount + 1
.cboEmployee.Undo
Cancel = True
End If
End With
End IF
End Sub

Private Sub txtPassword_AfterUpdate()
Docmd.OpenForm "BlaBlaBla"
Docmd.Close acForm, .Name, acSaveNo
End Sub

Now, because cboEmployee is bound to CurrentUser in tblLoginSettings, it
will be updated automatically. Also notice I included a retry count so if
the user enters an invalid password 3 time, the application automatically
closes.

This is how I would do it.
--
Dave Hargis, Microsoft Access MVP

Sure. I’d certainly appreciate any and all help with this! Basically, under
Tools > Startup, I have Display Form/Page, which shows frmLogon. This forces
this particular Form to show when Access starts up. Pretty basic stuff,
right. So now, with the Tables and other things hidden, only that Form
shows. On the frmLogon, I have a ComboBox, named ‘cboEmployee’, which loads
names from Row Source ‘SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY
tblLogin.Login;’. Pretty basic, right. This Form has a TextBox, where a
user can input their password. The Form also has a command button, named
‘cmdLogin’. Based on the user and the password, one of two forms opens, and
will eventually display links certain reports. Anyway, I’m trying to get the
user, from the ComboBox, into a Table, named ‘tblLoginSettings’.
‘tblLoginSettings’ consists of only a FieldName ‘ID’, which is Data Type
AutoNumber, and FieldName ‘Current User’, which is Data Type text. I was
hoping to be able to pass the variable from the ComboBox, named ‘cboEmployee’
to the ‘tblLoginSettings’ in the CurrentUser Field. Let’s say, for instance,
asmith is selected from the ComboBox, how can I transfer that variable to the
Table ‘tblLoginSettings’ and the Field ‘CurrentUser’? I guess it is kind of
complex. I’m not sure Access can do this, but I’ll bet it can…

It seems like you gave me the code to build that string. How do i pass that
value to the 'CurrentUser' Field in the ‘tblLoginSettings' Table? I’d
appreciate any help that you could offer on this.


Regards,
Ryan---


--
RyGuy

Can you describe what is it you want to do? I guess I am not clear on the
intent. If you can tell me what, perhaps I can tell you how.
--
Dave Hargis, Microsoft Access MVP

Oh! It's starting to make sense. Maybe I need to use a Query, rather than a
Table. Right now I have a Table named tblLoginSettings. It has a Field,
which is Data Type AutoNumber and another Field, which is Date Type Text.
That's all. i don't use a Query for this. Should l I use a Query for this?
I only have two Queries in this DB and they are actually related to this
Table. I was hoping to get the value from the ComboBox, pass it to the
Table, named tblLoginSettings, and pass that to one of the two Queries,
depending on what is entered into the Table. Does that make any sense? Is
there a better way of doing this?

Please let me know what to do.


Thanks,
Ryan--

--
RyGuy


:

You are not executing the SQL. All you are doing is building the SQL command.
Should be:

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

Thanks for following up Dave. This is the code that I am running in the Form
now:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS text;"

End Sub

The error is gone, but now nothing happens. The CurrentUser in
tblLoginSettings stays the same. The value from the ComboBox is never
actually passed. I know VBA in Excel pretty well, but I don't know VBA in
Access well. The code looks right to me, but I just can't tell what is
wrong. Perhaps it could be a setting somewhere...on the Form on in the
tlbLoginSettings? I think we car egetting very close to a solution here.
Any additional thoughts on this?

Thanks for your time,
Ryan--



--
RyGuy


:

My mistake, sorry. Should be:
strSQL = "INSERT INTO tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"

--
Dave Hargis, Microsoft Access MVP


:

Running the code below gives me a syntax error:
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.cboEmployee & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Error is:
Run-time error 3134:
Syntax Error in INSERT INTO statement.

This line is highlighted:
CurrentDb.Execute strSQL, dbFailOnError

What causes this error?

Regards,
Ryan---

--
RyGuy


:

Thanks for the look Dave! The code looks like it should work, but it
actually does not work.

The error message reads:
Compile Error:
Method or Data Member not Found

The Private line is yellow and the following is highlighted:
.MyCombName


Yes, CurrentUser is of Data Type text.

The Row Source on the ComboBox is:
SELECT DISTINCT tblLogin.Login FROM tblLogin ORDER BY tblLogin.Login;

Also, After Update is set to:
[Event Procedure]
(not sure if that makes a difference or not...)

The code under the UserForm is as follows;
'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
CurrentDb.Execute strSQL, dbFailOnError

Me.txtPassword.SetFocus

End Sub

Any additional thoughts?
Thanks,
Ryan---


--
RyGuy


:

I assume the combo box will be an unbound control and that tblLoginSettings
is not part of the form's record source. Use the combo's After Update event:

Dim strSQL As String

strSQL = "INSERT tblLoginSettings (CurrentUser) SELECT """ & _
Me.MyCombName & """ AS dummy;"
Currentdb.Execute strSQL, dbFailOnError

The above syntax assumes CurrentUser is a text field.
--
Dave Hargis, Microsoft Access MVP


:

I’d like to take a value from a combo box and load it into a table named
tblLoginSettings. I am trying to figure out how to get the value into a
field named ‘CurrentUser’ into the table named tblLoginSettings. Not sure
how to start. I guess it would be done by passing a variable, or passing a
parameter, or something like that. Can someone give me some code to do this,
or refer me to a web site that ex\plains how to do this?

Thanks so much,
Ryan---