HELP with UPDATE SQL in form

[alexmb1]

I have a combo box where you can select a residence out of and 'available residence' query. i created a yes/no column to indicate when a house was assigned and placed it in the 'employee' table that the available query is based on. when i click the residence in the box it goes into a field called 'proposed_assignment' then i click assign and it becomes assigned. at this point i need to automatically change the yes'no box in the employees form to 'yes' and thus exclude it from my query. in my 'employee' table the address i just selected to a previous employee so my code is such:

UPDATE [Employees]
SET [House re-Assigned] = true
WHERE [Permanent_Address] = [All Employees]![Assigned_unit]

again, permanent_address is the address i am using to locate the previous owner of the house in my table and change their re-assignment status to 'yes'. this way they are removed from the query.

after this i requery my combo box. this is not working and my same choices appear. also when i run my sql i get a pop up box to enter parameters.

suggestions please??

 

[DCrake]

Is permanent address an PK field or a string?

Your combo box should contain the PK for the address and be the bound column.

Your SQL should then read

SQL = "UPDATE [Employees] " & _
"SET [House re-Assigned] = True & " " _
"WHERE [EmplyeeID] = " & Me.ComboBox

It is the employees address you are changing I assume. As the employee can only live in one place at at any one time the employee table should contain a FK back to the address table. You should also record the Employee Id in the Address table. If the addresess can belong to more than one employee then you need to look at that also.

David

 

[alexmb1]

permanent address is a string. the PK is an automnumber ID field. The address can belong to more than one person i.e a husband and wife that are both employees. and yes it is the address i am changing. i am assigning new address to incoming employees. the old ones will com out of the form via a filter but remain in the table so they can be archived. thus, the employee table may contain several people listed at the same address but only those ones that are currently employed will appear on the form. this is the main working platform for the database.

 

[DCrake]

So Do you have a table with all the addresses in? If not you should have. This means that you only need to store the PK of the address in the Employee table. That way if something changes about the address then all changes will be cascaded down to all employees.

When you have an Employee on the screen and you want to assign the address you combo will have the PK (hidden) and the first line(s) of the address. The Pk will be the bound column, as as mentioned earlier you need to update the Address FK in the employee table with the PK in the Address table for that employee.


Like wise if you want to do a search on all employees who live at a specific address you would select an address in much the same way as you would do above and filter the employee by the Address FK.

David

 

[alexmb1]

also, i need to have the string address return and not the number. in the 'assigned_unit' field

 

[DCrake]

Why? You can use a query to get you this. If you must then you update query should reference ComboBox.Column in you SQL where n equals your column number. Remember column numbers a zero based.

David

 

[alexmb1]

yes i have a residence table that has all the info about the houses. it is in a relationship with the employee table through 'occupant' and 'employee'. ill work on it and get back to you

 

[alexmb1]

i suck at adding foreign keys to tables. and the online help hasne been very clear. each time i get a combo box and i cant get the right relationship established. in this case it is a many to one (employee to residence)

 

[alexmb1]

should i create another table or just add them directly to the existing tables

 

[alexmb1]

i keep getting a paramete box to input a residence id. fml

 

[alexmb1]

Well i found a way to make it all work much more simply just playing arund with setValue marcos. now it does what i need it to do and moves around just fine. thanks for your support though. looks like i still have a lot to learn but im getting there