Getting the value of an unbound field from another control in the form

[johnkrytus]

There is a lot going on in this form and there are likely a hundred much better ways to do it (I'm always open to suggestions)...

The recordsorce for timeline_entry_id is attached in a pic as is JobSiteID. What I am trying to do is populate JobSiteID WHERE company_id is = to the company_id in timeline_entry_id.

The query and form work until I add: WHERE [Forms]![TimeCard_sub]![timeline_entry_id].Column(3) - then the result of JobSiteID is blank.

 

[plog]

The recordsorce for timeline_entry_id is attached in a pic as is JobSiteID

That's ciruclar logic isn't it? I mean the form is based on that query, but that query is based on the form.

What I am trying to do is populate JobSiteID WHERE company_id is = to the company_id in timeline_entry_id.

I can't see how the form and the query relate to each other. If you want to populate something on the form that isn't in the table the form is based on, you should use a Dlookup.

 

[Galaxiom]

SQL can't refer to the Columns collection of a control

Create a textbox with the control source referring to the column then use the text box in the SQL.

 

[johnkrytus]

This is the rowsource for timeline_entry_id. It basically displays a dropdown of any companies that this person has ever been assigned to.

SELECT [CompanyName] & " - " & [start_date] AS Expr1, qry_assignment_companies.EntryId, qry_assignment_companies.EmployeeId, qry_assignment_companies.company_id
FROM qry_assignment_companies
WHERE (((qry_assignment_companies.EmployeeId) Like [Forms]![payroll]![EmployeeID]));

This is the rowsource for JobSiteID. Right now it displays ALL jobs in the table.

SELECT projects.SiteName, companies.CompanyName, projects.project_id
FROM projects INNER JOIN companies ON projects.company_id = companies.company_id
ORDER BY projects.SiteName;

What I want it to do is only show the jobs that are associated with the companies displayed in timeline_entry_id after its query has been run.

Is that a better explanation? ...I hope

edit: the word "rowsource"

 

[johnkrytus]

Create a textbox with the control source referring to the column

How do I refer to that column? "=[timeline_entry_id].[column](3)" is not working.

 

[arnelgp]

put the value in a textbox in a form, then include the textbox in your query or create a tempvar to hold the value of the column, use the tempvar in your query.

 

[GinaWhipp]

Note, that a Combo Box column order starts with 0(zero), so, if you want the third column you must use a 2.