Filling only certain fields with data. 1

[Rubius]

Ok this one is tough. I have a Query from a table which shows locations/items/counter etc rest doesn't matter.<br><br>I have it setup so that it only displays the items in the location I select in a subform of a form.<br>So I have one location and the list of items in front of me. NOW, each *location* has a different counter. So when I display that location, I want to then enter a number in the counter combo box and update ONLY the counter fields that are on display. I have been able to get it to change the WHOLE database of counter fields, but not JUST those counter fields that are on display with the items and the location. Note: All the counters for one location are the same. <br>Thanks and good luck, I've been racking my brains all day.

 

[DougP]

Well that is done by putting those records in a recordset and then updating only those records. Like in a For Next loop.<br>If they are in a subform then your recordset is a recordset clone.<br>You would then loop though each record, updating it.<br>----------------------------------------<br>Private Sub Command0_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim a As Integer, SomeValue As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;SomeValue = &quot;ChangeMe&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = Me![Companies subform].Form.RecordsetClone<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveLast<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;For a = 1 To rs.RecordCount<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs!Value1 = SomeValue&nbsp;&nbsp;&nbsp;&nbsp;'Change the vaalue here<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;rs.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>----------------------------------------<br>Piece of CAKE!!!<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[gol4]

update query based on same selection that brings locations into the form<br>something like this:<br><br>UPDATE yourtable&nbsp;&nbsp;SET yourtable.counters = ([Forms]![yourform]![counter])<br>WHERE ((([location]))=([Forms]![your form]![location])) <br><br>

 

[Rubius]

Thanks it works almost perfect. I'm now having a new problem, it seems when I goto update the 3 fields that I do this to, it adds the first record of the entire database to the current location...? Any idea's why it would do this??<br><br>And how do I get rid of the new record field since I don't want to add any new records.

 

[gol4]

To get rid of the extra new record field set the forms Allow additions property to no. If it is in a sub form do it on the sub form not the main form.