Combo Box in Datasheet View (FORM)

J

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!
 
S

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.
 
J

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;
 
J

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?

JK said:
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;





SteveM said:
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.
 
S

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


JK said:
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?

JK said:
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;





SteveM said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top