Combo Box Columns

[gjameson via AccessMonster.com]

I have a combo box on a login page which has 4 columns. Once the right user
id and password are entered the login page goes to a form that is unbound and
has a subform on it. The subform is supposed to show open tickets for the
person who logged in. I am having problems getting Column(3) from login to
the query that is to show their tickets. I have tried so many things I really
do not know where to go from here.

Query for login combo box:

SELECT tblEmployees.lngMyEmpID, tblEmployees.strWorkID, tblEmployees.
strEmpName, tblEmployees.strEmpID
FROM tblEmployees;


Query for subform:

SELECT [Maint Records].ID, [Maint Records].strEmpID, [Maint Records].
strWorkID, [Maint Records].[Date Issued], [Maint Records].Action, [Maint
Records].Problem, [Maint Records].[Date Resolved], [Maint Records].Status,
[Maint Records].[Ticket Number], [Maint Records].Notes
FROM tblEmployees INNER JOIN [Maint Records] ON tblEmployees.strEmpID =
[Maint Records].strEmpID
WHERE ((([Maint Records].strEmpID)=Forms!splash!cboEmployee.Column(3)))
ORDER BY [Maint Records].[Date Issued] DESC;

Help..................

TIA
Gerald

 

[Douglas J. Steele]

I don't believe SQL is capable of working with the Columns collection: it
simply doesn't know about VBA concepts like that.

You could try putting a hidden text box on the form, and populating it with
the contents of the combo box in its AfterUpdate event.

 

[Duane Hookom]

I think you can get by with an invisible text box with a control source of:
=cboEmployee.Column(3). Use the text box in your query criteria.

--
Duane Hookom
MS Access MVP

I don't believe SQL is capable of working with the Columns collection: it
simply doesn't know about VBA concepts like that.

You could try putting a hidden text box on the form, and populating it
with the contents of the combo box in its AfterUpdate event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a combo box on a login page which has 4 columns. Once the right
user
id and password are entered the login page goes to a form that is unbound
and
has a subform on it. The subform is supposed to show open tickets for the
person who logged in. I am having problems getting Column(3) from login
to
the query that is to show their tickets. I have tried so many things I
really
do not know where to go from here.

Query for login combo box:

SELECT tblEmployees.lngMyEmpID, tblEmployees.strWorkID, tblEmployees.
strEmpName, tblEmployees.strEmpID
FROM tblEmployees;


Query for subform:

SELECT [Maint Records].ID, [Maint Records].strEmpID, [Maint Records].
strWorkID, [Maint Records].[Date Issued], [Maint Records].Action, [Maint
Records].Problem, [Maint Records].[Date Resolved], [Maint
Records].Status,
[Maint Records].[Ticket Number], [Maint Records].Notes
FROM tblEmployees INNER JOIN [Maint Records] ON tblEmployees.strEmpID =
[Maint Records].strEmpID
WHERE ((([Maint Records].strEmpID)=Forms!splash!cboEmployee.Column(3)))
ORDER BY [Maint Records].[Date Issued] DESC;

Help..................

TIA
Gerald

 

[Allan Murphy]

Is Column(3) the third or fourth column in the query.
Remember columns in a query start at 0

 

[gjameson via AccessMonster.com]

I am using a text box on my login form with =cboEmployee.Column(3). This
works fine. It is displaying what I need. But this is not getting over to the
other sub-form with the query. I have a txt box on the form that the sub-form
is on and it shows the contents fine too. Is this a problem with having the
login form unbound? When I login and go to the other form it prompts me for
my txtStrID, I enter it and the sub-form query works.

So close.............

TIA
Gerald

I think you can get by with an invisible text box with a control source of:
=cboEmployee.Column(3). Use the text box in your query criteria.

I don't believe SQL is capable of working with the Columns collection: it
simply doesn't know about VBA concepts like that.

[quoted text clipped - 35 lines]

 

[Duane Hookom]

What do you mean by "getting over to the other sub-form with the query" and
what is txtStrID?


--
Duane Hookom
MS Access MVP

I am using a text box on my login form with =cboEmployee.Column(3). This
works fine. It is displaying what I need. But this is not getting over to
the
other sub-form with the query. I have a txt box on the form that the
sub-form
is on and it shows the contents fine too. Is this a problem with having
the
login form unbound? When I login and go to the other form it prompts me
for
my txtStrID, I enter it and the sub-form query works.

So close.............

TIA
Gerald

I think you can get by with an invisible text box with a control source
of:
=cboEmployee.Column(3). Use the text box in your query criteria.

I don't believe SQL is capable of working with the Columns collection: it
simply doesn't know about VBA concepts like that.

[quoted text clipped - 35 lines]

TIA
Gerald

 

[gjameson via AccessMonster.com]

Once the right user id and password are entered the login page that closes
and a form that is unbound and
has a subform is opened. The subform is supposed to show open tickets for the
person who logged in.

login form to maint form, with a subform, which is filtered by a query,
depending on who logged in on the login form

txtStrID is the hidden text box on my login form
[Forms]![Splash]![txtStrID] is the criteria in my query

What do you mean by "getting over to the other sub-form with the query" and
what is txtStrID?

I am using a text box on my login form with =cboEmployee.Column(3). This
works fine. It is displaying what I need. But this is not getting over to

[quoted text clipped - 21 lines]

 

[Duane Hookom]

Are you leaving the Splash form open?


--
Duane Hookom
MS Access MVP

Once the right user id and password are entered the login page that closes
and a form that is unbound and
has a subform is opened. The subform is supposed to show open tickets for
the
person who logged in.

login form to maint form, with a subform, which is filtered by a query,
depending on who logged in on the login form

txtStrID is the hidden text box on my login form
[Forms]![Splash]![txtStrID] is the criteria in my query

What do you mean by "getting over to the other sub-form with the query"
and
what is txtStrID?

I am using a text box on my login form with =cboEmployee.Column(3). This
works fine. It is displaying what I need. But this is not getting over
to

[quoted text clipped - 21 lines]

TIA
Gerald

 

[gjameson via AccessMonster.com]

Closing it when I goto the other form. Should I leave it open?

Are you leaving the Splash form open?

Once the right user id and password are entered the login page that closes
and a form that is unbound and

[quoted text clipped - 18 lines]

 

[Duane Hookom]

If you want to reference a control's value from the form, you must leave it
open. You can make it invisible by setting the form's visible property to
False.

--
Duane Hookom
MS Access MVP

Closing it when I goto the other form. Should I leave it open?

Are you leaving the Splash form open?

Once the right user id and password are entered the login page that
closes
and a form that is unbound and

[quoted text clipped - 18 lines]

TIA
Gerald

 

[gjameson via AccessMonster.com]

I left it open and it is still asking for that value.

If you want to reference a control's value from the form, you must leave it
open. You can make it invisible by setting the form's visible property to
False.

Closing it when I goto the other form. Should I leave it open?

[quoted text clipped - 6 lines]

 

[gjameson via AccessMonster.com]

Thanks for your help Duane. I must stress the importance of using the right
fields in 'Link Child and Master fields'. This is where my typo was. I was
still referencing the wrong field in one. Works great now.

I left it open and it is still asking for that value.

If you want to reference a control's value from the form, you must leave it
open. You can make it invisible by setting the form's visible property to

[quoted text clipped - 5 lines]