Textbox control source headache

  • Thread starter Thread starter Jonathan Brown
  • Start date Start date
J

Jonathan Brown

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
 
Jonathan

Another approach might be to create a query that returns each employee's MAX
[startdate] (a TOTALS query).

Then you could use a query against that query, using the form's EmployeeID
as a criterion.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
 
I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.
 
No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

Klatuu said:
Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
Click to expand...
Click to expand...
 
When I put an equal sign I get a syntax error: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

If I include the equal sign and then enclose the rest of it in parentheses I
the #name? error message again.

Klatuu said:
No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

Klatuu said:
Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
--
Dave Hargis, Microsoft Access MVP


:

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
I am going to have to set up a test situation and see what it needs. I'll
get back to you later today.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
When I put an equal sign I get a syntax error: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

If I include the equal sign and then enclose the rest of it in parentheses I
the #name? error message again.

Klatuu said:
No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

:

Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
--
Dave Hargis, Microsoft Access MVP


:

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
Here's how i've got it setup:

tblEmployees
------------------------
EmpNum, Autonumber, PK
Name, Text
....

tblSites
---------------------
SiteNum, autonumber, PK
SiteName, text
....

tblSHistory
-----------------
SHistoryNum, autonumber, PK
SiteNum, FK to tblSites
EmpNum, FK to tblEmployees
StartDate

Thanks for your help.

Just a text box that displays the employee's current site name being that
it'd be the last record or record with the most current startdate in the site
history table for a given employee.


Klatuu said:
I am going to have to set up a test situation and see what it needs. I'll
get back to you later today.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
When I put an equal sign I get a syntax error: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

If I include the equal sign and then enclose the rest of it in parentheses I
the #name? error message again.

Klatuu said:
No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


:

I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

:

Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
--
Dave Hargis, Microsoft Access MVP


:

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
I am able to get the result I'm looking for if I run a query with the
following select statement, but I can't get it to appear in a text box on a
form:

SELECT TOP 1 tblSHistory.SiteNum
FROM tblSHistory
WHERE (((tblSHistory.EmpNum)=388288))
ORDER BY tblSHistory.StartDate DESC;

388288 is an employee number I used. Somehow I need to replace it with
me.empnum, or forms!frmFSRInfo!empnum.

Jonathan Brown said:
Here's how i've got it setup:

tblEmployees
------------------------
EmpNum, Autonumber, PK
Name, Text
...

tblSites
---------------------
SiteNum, autonumber, PK
SiteName, text
...

tblSHistory
-----------------
SHistoryNum, autonumber, PK
SiteNum, FK to tblSites
EmpNum, FK to tblEmployees
StartDate

Thanks for your help.

Just a text box that displays the employee's current site name being that
it'd be the last record or record with the most current startdate in the site
history table for a given employee.


Klatuu said:
I am going to have to set up a test situation and see what it needs. I'll
get back to you later today.
--
Dave Hargis, Microsoft Access MVP


Jonathan Brown said:
When I put an equal sign I get a syntax error: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

If I include the equal sign and then enclose the rest of it in parentheses I
the #name? error message again.

:

No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


:

I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

:

Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
--
Dave Hargis, Microsoft Access MVP


:

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
Okay, this is what I've done. Kind of a two step process but it works. I
doubt it's the proper way to do it but here goes.

I made the following query:

SELECT TOP 1 tblSHistory.EmpNum, tblSites.SiteName
FROM tblSites INNER JOIN tblSHistory ON tblSites.SiteNum = tblSHistory.SiteNum
WHERE (((tblSHistory.EmpNum)=[forms]![frmfsrinfo]![empnum]))
ORDER BY tblSHistory.StartDate DESC;

Then I added a listbox control to my form and set my query (qrySiteName) as
the row source. Set column count to 2 and hid the first column; 0";.5". I
then locked the control. And then on the forms On Current event I put
Me.lstSiteName.Requery.

I was able to do the same with a combo box but the text didn't appear unless
I clicked on the arrow to drop down the list.

If you can think of a better, more efficient way of doing this, I'd love to
hear it.

Jonathan Brown said:
I am able to get the result I'm looking for if I run a query with the
following select statement, but I can't get it to appear in a text box on a
form:

SELECT TOP 1 tblSHistory.SiteNum
FROM tblSHistory
WHERE (((tblSHistory.EmpNum)=388288))
ORDER BY tblSHistory.StartDate DESC;

388288 is an employee number I used. Somehow I need to replace it with
me.empnum, or forms!frmFSRInfo!empnum.

Jonathan Brown said:
Here's how i've got it setup:

tblEmployees
------------------------
EmpNum, Autonumber, PK
Name, Text
...

tblSites
---------------------
SiteNum, autonumber, PK
SiteName, text
...

tblSHistory
-----------------
SHistoryNum, autonumber, PK
SiteNum, FK to tblSites
EmpNum, FK to tblEmployees
StartDate

Thanks for your help.

Just a text box that displays the employee's current site name being that
it'd be the last record or record with the most current startdate in the site
history table for a given employee.


Klatuu said:
I am going to have to set up a test situation and see what it needs. I'll
get back to you later today.
--
Dave Hargis, Microsoft Access MVP


:

When I put an equal sign I get a syntax error: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

If I include the equal sign and then enclose the rest of it in parentheses I
the #name? error message again.

:

No, but you do need an = sign in front of it.
--
Dave Hargis, Microsoft Access MVP


:

I'm still getting the #Name? error message. However, I just selected your
text and pasted it and changed the form name to match my form name. In the
control boxes' control sources don't we have to add some extra ()'s? or []'s?

All the SQL select queries seem to be formatted differently if it's for a
query, a control source, row source or in code.

:

Select tblshistory.sitename
From tblShistory
Where tblShistory.startdate = Dmax("startdate", "tblSHistory", "empnum = " &
Forms!MyFormName!empnum )

Note: Queries do not understand Me. You have to fullly qualify the control
as shown above.
--
Dave Hargis, Microsoft Access MVP


:

I have an employees table and a sitehistory table. 1 to many relationship.
One employee may have been assigned to more than one site historically.

I want to place a textbox on a form that displays the site that the employee
is currently assigned to.

I'm thinking that the select statement should look something like:

Select tblshistory.sitename
From tblShistory
Where tblshistory.empnum = me.empnum AND Dmax("startdate")

But that doesn't seem to work I keep getting a #Name error.

Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top