New to access and need help in a form Tried [="[EmployeeID]=" & Str(Nz([Screen].[ActiveControl],0))]

[WarehouseKeith]

I have a query for active employee numbers and names. I am trying to create a form with two fields (employee number and employee name) for a driver, swamper1, and swamper2(six fields in total) all form the same query. The user selects the employee number from a combo box and the "name" field auto populates. I tried this macro



[="[EmployeeID]=" & Str(Nz([Screen].[ActiveControl],0))]

but it only works for the first two fields, and then the name just duplicates for swamper1 and swamper2.
As I stated I am very new to Access and have tried searching the web, but this is a difficult search to word correctly. Hopefully someone here can point me in the right direction.

Thanks

[June7]

Multiple similar name fields indicates data structure is not normalized. This will cause lots of frustration.

Not sure what you mean by 'the name just duplicates'.

[ssanfu]

Would you post the SQL of the query?

[WarehouseKeith]

I have a table that has all employees(active or inactive), I have a query from this table that shows only active employees for other forms and reports. I am trying to develop a logistics facet to the database. I would have up to 3 employees on a delivery route so I want create a form where the user can enter this(as well as a host of other info) from my already existing tables and queries. I hope this helps explain what I am trying to accomplish.

[WarehouseKeith]

SELECT EmployeeInformation.EmployeeID, EmployeeInformation.FirstName, EmployeeInformation.LastName, Schedules.StartTime, EmployeeInformation.Task
FROM EmployeeInformation INNER JOIN Schedules ON EmployeeInformation.EmployeeID = Schedules.EmployeeID
WHERE (((EmployeeInformation.Status)="1"));

[June7]

You have multiple fields in Schedules table for up to 3 employees? You would have to apply the same filter criteria to all 3 employee fields using OR operator.

I suspect the reason for 'the name just duplicates' is that textboxes are bound to the same field.

As already noted, non-normalized structure will be problematic.

[WarehouseKeith]

Sorry for the large attatchments. I am trying to create a data entry form for route informationtbl that draws from employee informationtbl.I very well may be going about this the wrong way. I will need to be able to check a specific date and know which employees were on which trucks.

[June7]

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be DriverEmpNum.

I don't use macros. I've never used Screen.ActiveControl.

DriverName field should not be in RouteInfoTbl, just employee number. Same for Swamper1Name.

Options for displaying the employee info related to employee number:

1. include the employee table in the form RecordSource, join type "Include all records from RouteInfoTbl ..." - however, this would require including the EmployeeInfoTbl in the query 3 times, one for each employee field

2. multi-column combobox and then an expression in textbox can reference column of combobox

3. DLookup()

What exactly is the issue you are dealing with? If it's filtering a query, already commented on that.

[ssanfu]

I would suggest looking at normalizing the table structures a little more.

In the "RouteInfoTbl", you have "DriverEmpNum" and also "DriverName". I would guess that "DriverEmpNum" is the FK linked to "EmployeeInformationTbl.EmployeeID" (PK). The driver name can be derived from the link, so it is redundant in the "RouteInfoTbl". Same goes for "Swamper1Name"

In "RouteInfoTbl", there are fields for 3 employees. What happens if you have to add a 4th employee, say "DriverApprentice"? The design of the structure should be such that the dB doesn't have to be redesigned if another job type is added.

Also, be aware of reserved words in Access. "Type" is one example. Here is a list of reserved words:
http://www.allenbrowne.com/AppIssueBadWord.html


Sorry, I'm not trying to trash you... just a couple of things I see.

I'm now paying for design errors in a dB that is live. I need to add a new sub type, but didn't design the table correctly. So I get to spend a couple of weeks revising all queries, forms and reports.

[WarehouseKeith]

Thanks to all for your help. I will be trying some, if not all, of your suggestions over the next few days and hopefully will be able to mark this thread as solved.