Update Records 2

[sha123]

I have a form with these fields:

1)Product
2)Qty
3)Area

In the area field there is 3 options:

1)GAU
2)WC
3)NAT

The problem i am sitting with is that say in record 1 i will have GAU in the area field and in record 3000 I will have WC in the area field.

I now need to update all the records(empty) in between that will belong to GAU.

But I need it to stop updating when it gets WC, and then it needs to fill the area between WC and NAT with WC!

How will I be able to do that???

 

[KenReay]

Hi

Do you mean the record with product code 3000, or do you mean the 3000th record?, if the latter, it depends on the sort order of your table.

define more clearly the rule for determining Area and someone will be able to help you

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sha123]

It is the 3000th Record! A product can belong to all 3 regions.
All I am trying to achieve is to determine the qty of products that was sold to area 1)GAU 2)WC 3)Nat?

And for that I need the area next to each record in my table!



Hope this is more clear

 

[KenReay]

Hi

Not really

Do you mean you can have records so:

Product qty Area
0001 3 GUA
0001 5 WC
0001 4 NAT

or do you mean you ahve records with Product and qty populated, but not area and you ahve to somehow 'magically' populate the Area based on their 'physical' position in the table, if the latter do you ahve any means to determine their physiacl position in the table, except the .absoluteposition property, because idf this is the case you have a problem, in SQL physical order does not exist, you have to use an order by clause, and you have nothing to order by

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sha123]

This is what my table looks like:

Product Qty Area
0001 50 GAU
0002 10
0003 5
0004 70
0005 80
0002 30 WC
0009 5
0010 20
0004 15
0005 30 Nat
0002 15
0008 5
0001 25

I just want to populate the area field between the two areas.

Say the first area = "GAU" and the second "WC" then all the fields between area "Gau" and "WC" should be "GAU".

Hope this is more clear now!

 

[randy700]

How about something like this...

Dim db as Database
Dim rs as Recordset
Dim strArea as String
Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableName")
With rs
.MoveFirst
strArea = rs!Area
.MoveNext
Do Until .EOF
If Not IsNull(rs!Area) And rs!Area <> "" Then
.Edit
rs!Area = strArea
.Update
End If
.MoveNext
Loop
End With
Set rs = Nothing
Set db = Nothing


Randy

 

[KenReay]

Hi

would it not be, as you have but:

Do Until .EOF
If IsNull(rs!Area) or rs!Area = "" Then
.Edit
rs!Area = strArea
.Update
Else
strArea = rs!Area
End If
.MoveNext

however I am concerned about the sequence in which the rows will be processed, it is my understanding that in the absence of an order by clause in the SQL, the sequence of the rows returned is indeterminate, so it may by chance work, but then again it may not

be sure to take a copy before you run it is my advice.

A final point, you need a reference to the DAO library to use the suggested code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sha123]

Thanks Guys.

What I have done is added a ID(AutoNumber) in the table, I then said:

If ID = "1" Then
Area = "WC"
Else
If ID = "2" Then
Area = "GAU"
End If
End If

Thanks for making it a lot more clear to me!