Can't update data in my Query

[drusteeby]

So heres whats happening. I have two tables, both of them have a serial number for a certian part. In one table I have all of the data for that part and in the other table I have the Repair history for that part. Well in getting the parts repaired sometimes they go back into different slots, which is a data point on my table. I currently have a form set up with a query in a sub-form. On the form there is a combo box that you can select the serial number with and the query runs with that serial number and brings up all the data. Now when I add the Current slot number from the first table on to the query it shows all the data but it wont let me add a new line to the data. What am i doing wrong?

 

[pbaldy]

Your answer might be here:

http://allenbrowne.com/ser-61.html

or here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;328828

 

[drusteeby]

I tried a few of those things that i thought might be it and none of them worked. Now when I make the query only pull data from my second table I can edit it but not when I add the related data from the first table in it. Any idea?

All your help is appreicated.

 

[drusteeby]

I just read that you Could edit the data if you created a one-to-many realationship with it, but would I have to enforce the integrety? It wont let me do that because I have null values in some of my boxes on the first table, because not all parts have a serial number.

 

[pbaldy]

What's the SQL of the query?

 

[drusteeby]

SELECT History.[Serial Number], History.Recieved, History.[Sent Out], History.NCR, History.[Old Slot], History.[New Slot], [VME Chassis Contents].[Slot Number]
FROM History, [VME Chassis Contents]
WHERE (((History.[Serial Number])=forms!frmRepairHistory.cmboRepairHistory) And (([VME Chassis Contents].[Serial Number])=forms!frmRepairHistory.cmboRepairHistory));

 

[drusteeby]

Essentially what I want to do is take the value in "New Slot" that the user enters in on the most recent repair history log and update that to the current slot on the other table. With this SQL I was trying to do two seperate colunms and have the user update the current slot manually but it wouldnt let me update the data.

 

[pbaldy]

From the second link:

When the query includes more than one table or one query, and the tables or the queries are not joined by a join line in Design view, you cannot update data in the query. To resolve this problem, you must join the tables correctly so you can update them.

 

[drusteeby]

How do I join them correctly?

 

[pbaldy]

Try clicking and dragging between the respective Serial Number fields. That appears to be the field they have in common?

 

[drusteeby]

Tried that, no luck. Is there a way I can just make the "New Slot" field update the corresponding field in the other table without showing it in this query. So when they type in the new slot on the form it automatically updates to the current form?

 

[pbaldy]

Something like:

CurrentDb.Execute "UPDATE TableName SET NewSlot = " & Me.NewSlot & " WHERE SerialNumber = " & Me.SerialNumber

Presumably run from the after update event of that control on the form.

 

[drusteeby]

Something like:

CurrentDb.Execute "UPDATE TableName SET NewSlot = " & Me.NewSlot & " WHERE SerialNumber = " & Me.SerialNumber

Presumably run from the after update event of that control on the form.

I'm a bit confused, what is supposed to be inbetween the ' " & '?

 

[pbaldy]

You would need to change the table and field names as appropriate, and the names of the controls on the form containing the relevant values (like Me.NewSlot).

 

[drusteeby]

So what would be the correct value for, say i have this



Form:

Combo Box with serial number:


Subform with a Query:

Serial number Old Slot New Slot
2151651 10 11
2151651 11 12

What is the name of the feild with the 12 in it, the last in the New Slot Column?

 

[pbaldy]

Does this help?

CurrentDb.Execute "UPDATE TableName SET NewSlot = " & Me.NewSlot & " WHERE SerialNumber = " & Me.SerialNumber

The name of the table you want to update

The names of the fields in that table containing the value to be updated and the value identifying which record to update

The names of the controls on the form containing the new value and the value identifying which record to update

 

[drusteeby]

What would the last value in a coulmn in a query search be named?

 

[drusteeby]

CurrentDb.Execute "UPDATE [VME Chassis Contents] " _
& "SET [New Slot] = Me.Combo44 " _
& "WHERE [Serial Number] = Me.cmboRepairHistory;"

I get error too few paramaters, expected 2. Whats wrong?

 

[drusteeby]

CurrentDb.Execute "UPDATE [VME Chassis Contents] SET [Slot Number] = " & Me.Combo44 & " WHERE [Serial Number] = " & Me.CmboRepairHistory

Now I have it down to expected 1

 

[pbaldy]

What are the data types of those 2 fields?