Problems with dbOpenSnapshot 2

[ffleitas]

Hello programmers,

I am having a terrible time with trying to use the dbOpenSnapshot. Here is a code and please feel free to correct the many mistakes I made:

Private Sub Form_Close()
Dim db As DAO.Database

Dim rs As DAO.Recordset
Set rs = Me.Recordset
Dim i As Integer
Dim j As Integer
Dim rec As Recordset
Dim stDocName As String
Dim strSQL As String

stDocName = "qryPickupSubPlusQnty"
strSQL = "Select * FROM tblPickupsSub Where [PickupID] = Me![PickupID]"


Set db = CurrentDb
Set rs = db.OpenRecordset("tblPickupsSub", dbOpenDynaset)
Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rec.MoveFirst
While Not rec.EOF


If Me!frmPickupsOnCallSub![Quantity] > 1 Then


With rs
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew
.Fields("PickupID&quot = Me![PickupID]
.Fields("ContainerID&quot = Me!frmPickupsOnCallSub![ContainerID]
.Fields("BarCode&quot = Me!frmPickupsOnCallSub![BarCode]
.Fields("ContainerName&quot = Me!frmPickupsOnCallSub![ContainerName]
.Fields("ContainerDescription&quot = Me!frmPickupsOnCallSub![ContainerDescription]
.Fields("Quantity&quot = 1
.Update
Select Case i
Case 4, 8, 12, 16: j = j + 1
End Select
Next i
'rs.MoveNext
'Ends the With Area above
End With


'Interior part of calculating Quantity
Else


'End of Interior Part of Calculating Quantity
End If

rec.MoveNext
Wend


rs.Close
Set rs = Nothing
Set db = Nothing


DoCmd.OpenQuery stDocName
End Sub

 

[Rolliee]

FF,
A snapshot is not editable.


R

 

[danvlas]

If I'm not blind, you open your snapshot, navigate through it but you're not doing absolutely anything with it...
You have:

Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
rec.MoveFirst
While Not rec.EOF
'blah blah
rec.MoveNext
Wend

That's all you have involving rec. No fields inspected, no record counting. You opened the door, walked to and fro a few times and not even closed it...Mind the mosquitoes

So what is the problem? what is it supposed to do???
Anyway, if you however attempt to write data to the snapshot, see Rollie's post. You can't (in DAO).

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Rolliee]

Daniel,

The .addnew is meant to add a new record to the recordset. It is just that a snapshot is a read-only,pass thru once data set.

Rollie E

 

[danvlas]

Rollie...
Just look at this:

Set rs = db.OpenRecordset("tblPickupsSub", DbOpenDynaset)

With rs
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew

rs (which the poster is trying to add records to is a DYNASET, which is a dynamic recordset, you can edit data, you can add data, you can delete data.

It's rec that is a snapshot "read-only,pass thru once data set blah blah"...but the poster doesn't do ANYTHING with it. Except opening it and moving around through it.

Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)


Please, read the code once again. And believe me...I know what a snapshot type recordset is.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Rolliee]

Felix and Daniel,

the form rs.fields() which Felix uses is the position of the field.

rs.addnew
rs.Fields(0)= Me![PickupID] 'not Pick...ID
rs.update

This ought to be the position in the table with the top spot = 0 and so forth. I see him as enteriing a new record and then setting it's values accordin the the variables on the form "me."

What am I missing???

Rollie E

 

[ffleitas]

Hi Rolliee,

I tried your suggestion as follows but I still get the same result as before. Basically, what I need the code to do is if the user in the subform area enters quantity 3 the code is to create these in units of one. The problem I am having is if there is more than one row of data in the subform area only the last row gets coded. I need for it to somehow check all the rows in the subform area and process each one and according to their quantities and create them as separate units of one. Here are the changes you kindly gave me and applied them but I get the same results as before.


If Me!frmPickupsOnCallSub![Quantity] > 1 Then

With rs
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew
'.Edit
'.Fields("PickupID&quot = Me![PickupID]
.Fields(0) = Me![PickupID]

'.Fields("ContainerID&quot = Me!frmPickupsOnCallSub![ContainerID]
.Fields(2) = Me!frmPickupsOnCallSub![ContainerID]

'.Fields("BarCode&quot = Me!frmPickupsOnCallSub![BarCode]
.Fields(3) = Me!frmPickupsOnCallSub![BarCode]

'.Fields("ContainerName&quot = Me!frmPickupsOnCallSub![ContainerName]
.Fields(4) = Me!frmPickupsOnCallSub![ContainerName]

'.Fields("ContainerDescription&quot = Me!frmPickupsOnCallSub![ContainerDescription]
.Fields(5) = Me!frmPickupsOnCallSub![ContainerDescription]

'.Fields("Quantity&quot = 1
.Fields(6) = 1
.Update
Select Case i
Case 4, 8, 12, 16: j = j + 1
End Select
Next i

'Ends the With Area above
End With


'Interior part of calculating Quantity
Else


'End of Interior Part of Calculating Quantity
End If



Set rs = Nothing
Set db = Nothing



DoCmd.OpenQuery stDocName

 

[danvlas]

Hmmmm....so you have a row in your subform that has a quantity of 5...
You want to split that row into 5, each having a quantity of 1...

Let's use the AfterUpdate event of the subform:


'=======Code Start===========================
Private Sub Form_AfterUpdate()
Dim Qty As Integer 'variable to detect the quantity
Dim MyArray(Me.RecordsetClone.Fields.Count) 'array to store all values in the fields

If Me("Quantity&quot>1 Then
'run code only for quantities greater than 1

Qty = Me("Quantity&quot
'store the value

With Me.RecordsetClone
'use subform's recordset
For i = 0 To .Fields.Count - 1
MyArray(i) = .Fields(i)
'add each value to the array
Next

'================================
'Current record should be changed
'================================
.Edit '(not needed in ADO)
.Fields("Quantity&quot = 1
.Update


'==================================================
'generate a loop to copy the values to new records
'==================================================
While Qty > 1
.AddNew

'======================================================
'get the values from the array and write the new record
'======================================================
For i = 0 To .Fields.Count - 1
.Fields(i) = MyArray(i)
Next

'================================
'Now update the quantity to 1
'================================
.Fields("Quantity&quot = 1
.Update

'================================
'decrease Qty by 1 unit
'================================

Qty = Qty - 1

'====================
'resume loop
'====================
Wend
End If
End Sub
'=======Code End===========================

I still don't understand what the 'snapshot' problem was...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ffleitas]

Hi,

Thanks for responding. I tried your code above and I get this error: Constant expression required. Can you assist me with the error?

Thanks,
Felix

 

[danvlas]

OOOPS...
Replace:
Dim MyArray(Me.RecordsetClone.Fields.Count) 'array to store all values in the fields
with
ReDim MyArray(Me.RecordsetClone.Fields.Count)




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ffleitas]

Hi again,

now I get another error:

Compile error
End if without block if

I check for any other if statements and I only found this one:
If Me("Quantity&quot > 1 Then

And at the bottom I believe it is closed off with this
End If

Thanks,

 

[danvlas]

End With

right before End If




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html