Combo Box in Datasheet View (FORM)

[JK]

I took the MS Service Call Mgt. Template and created a database for our
Service Department. On the main screen I added a command button that opens a
form in datasheet view and displays all of the open workorders in the system.
Using this form is much easier than looking up the customer and then opening
the workorder screen.

The problem is, I can't use a combo box to change the technician (or
employee) in this Open Workorders form. When I created the form it made the
tech field a txt field even though in the workorders screen its a combo box.
I then created a new combo box field so that all of our techs show up. When I
try to change the value, my computer beeps and doesn't allow me to change it.

I thought maybe someone could help me?

Thx!

 

[SteveM]

Is the form's AllowEdits property set to 'Yes'?

Is the form based on a query or table?
If a query, is it updateable?

If you are not sure, paste the SQL of your query in reply.

 

[JK]

I guess I'm not sure. Although I can tell you, I am able to update some
fields in the datasheet form, but not the technician field (whether it's the
text field or the combo box field.)

This is the SQL behind the form (query) record source:
Below this, I've pasted the SQL behind the combo box (query

SELECT DISTINCTROW Customers.AccountNumber, Customers.CompanyName,
Customers.ContactFirstName AS [Contact Name], Customers.PhoneNumber,
Workorders.WorkorderID, Workorders.DateReceived, Workorders.DateRequired,
Workorders.MakeAndModel, Workorders.ProblemDescription, [LastName] & ", " &
[FirstName] AS Tech, Workorders.DateFinished, Workorders.DatePickedUp
FROM (Customers INNER JOIN Workorders ON Customers.CustomerID =
Workorders.CustomerID) INNER JOIN Employees ON Workorders.EmployeeID =
Employees.EmployeeID
WHERE (((Workorders.DateFinished) Is Null))
ORDER BY [LastName] & ", " & [FirstName];


Combo Box Query Row Source

SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees;

 

[JK]

Actually, I think I know why this is happening, I just don't know how to fix
it. This is becuase the 'Tech' field is made up of two other fields. It's not
a direct link to the table - right?

I guess I'm not sure. Although I can tell you, I am able to update some
fields in the datasheet form, but not the technician field (whether it's the
text field or the combo box field.)

This is the SQL behind the form (query) record source:
Below this, I've pasted the SQL behind the combo box (query

SELECT DISTINCTROW Customers.AccountNumber, Customers.CompanyName,
Customers.ContactFirstName AS [Contact Name], Customers.PhoneNumber,
Workorders.WorkorderID, Workorders.DateReceived, Workorders.DateRequired,
Workorders.MakeAndModel, Workorders.ProblemDescription, [LastName] & ", " &
[FirstName] AS Tech, Workorders.DateFinished, Workorders.DatePickedUp
FROM (Customers INNER JOIN Workorders ON Customers.CustomerID =
Workorders.CustomerID) INNER JOIN Employees ON Workorders.EmployeeID =
Employees.EmployeeID
WHERE (((Workorders.DateFinished) Is Null))
ORDER BY [LastName] & ", " & [FirstName];


Combo Box Query Row Source

SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees;

Is the form's AllowEdits property set to 'Yes'?

Is the form based on a query or table?
If a query, is it updateable?

If you are not sure, paste the SQL of your query in reply.

 

[SteveM]

You got it.

Change the 'Tech' field to EmployeeID.

Then, when you change the selection in the combo, the EmployeeID (bound
column)is saved in the field. If you have the first column width set to '0'
then you will see the next column. Your combo will show the name
corresponding to the EmployeeID.

--
Steve McGuire
MCSD, MCAD, MCP

Actually, I think I know why this is happening, I just don't know how to fix
it. This is becuase the 'Tech' field is made up of two other fields. It's not
a direct link to the table - right?

I guess I'm not sure. Although I can tell you, I am able to update some
fields in the datasheet form, but not the technician field (whether it's the
text field or the combo box field.)

This is the SQL behind the form (query) record source:
Below this, I've pasted the SQL behind the combo box (query

SELECT DISTINCTROW Customers.AccountNumber, Customers.CompanyName,
Customers.ContactFirstName AS [Contact Name], Customers.PhoneNumber,
Workorders.WorkorderID, Workorders.DateReceived, Workorders.DateRequired,
Workorders.MakeAndModel, Workorders.ProblemDescription, [LastName] & ", " &
[FirstName] AS Tech, Workorders.DateFinished, Workorders.DatePickedUp
FROM (Customers INNER JOIN Workorders ON Customers.CustomerID =
Workorders.CustomerID) INNER JOIN Employees ON Workorders.EmployeeID =
Employees.EmployeeID
WHERE (((Workorders.DateFinished) Is Null))
ORDER BY [LastName] & ", " & [FirstName];


Combo Box Query Row Source

SELECT Employees.EmployeeID, [LastName] & ", " & [FirstName] AS Expr1
FROM Employees;

Is the form's AllowEdits property set to 'Yes'?

Is the form based on a query or table?
If a query, is it updateable?

If you are not sure, paste the SQL of your query in reply.

--
Steve McGuire
MCSD, MCAD, MCP


:

I took the MS Service Call Mgt. Template and created a database for our
Service Department. On the main screen I added a command button that opens a
form in datasheet view and displays all of the open workorders in the system.
Using this form is much easier than looking up the customer and then opening
the workorder screen.

The problem is, I can't use a combo box to change the technician (or
employee) in this Open Workorders form. When I created the form it made the
tech field a txt field even though in the workorders screen its a combo box.
I then created a new combo box field so that all of our techs show up. When I
try to change the value, my computer beeps and doesn't allow me to change it.

I thought maybe someone could help me?

Thx!