Hi,

I have an order form (order header) and subform (order detail). When the order form is activated, it is in a data-entry mode (i.e. don't have access to previous orders).

I am not too familiar with recordsets. Can I use recordsets to populate some information on the order form/subform, but only update the tables when the user clicks on the "confirm" button?

If so, how can I do that?

Thank you

Chantal

Dim rec as ADODB.RecordSet
Dim SQL
SQL = _
"SELECT tblName.* " & _
"FROM tblName " & _
"WHERE tblName.FieldName = " & cboBox & ";" 'this will restrict your records by a cbobox on main form
Set rec = New ADODB.RecordSet
rec.Open "tblName",CurrentProject.Connection, adOpenStatic, adLockOptimistic

MainForm.txtName = rec!fieldName
MainForm.txtName2 = rec!fieldName2
MainForm.txtName3 = rec!fieldName3
MainForm.txtName4 = rec!fieldName4
 This is a rough idea, if you can explain specifically, what or how many fields from the subform will be used to populate how many fields on main form.

Actually, you don't even need to use a recordset, simply
MainForm.txtName = subfrmName.fieldName
would suffice, on the click of a button, if your updating from subform to mainform?
Or are you trying to filter the subform.
If populate, where does the info come from.
Yes, I just jumped around a lot.

Reply, if more info needed!

You are right, I didn't give a whole lot of details...

In my form, I have the order header information (cust name and no, order no, date of the order, type of delivery, type of payment, special charges /rebate)

In the order detail (subform) there is the item no and order quantity. When an item is selected, the item info (description, quantity available, unit price) is displayed on the same line.
Once the quantity is selected, the inventory table is updated.

There is a "confirm" control button in the main form. When the user clicks it, the total of the order is calculated and displayed on the main form.

The thing is that by updating the inventory table when an item and order quantity is selected it doesn't always update the table (order quantity and status of the item) properly and I can't seem to find why. I tested on and on and most of the time the inventory is fine but there is the odd time when it doesn't do the job... So I was wondering if it was better to do the updating when the user clicks on the "confirm" button (main form) ... and if so, how can I do it? Would recordsets be the answer ?

Let me know if you need more information.
Thanks!

chacquard, when the update is incorrect, does it remain incorrect, even after the form is reloaded or refreshed? Sometimes there's a delay in updating tables, while editing a form?
What event on {orderQuantity] do you make the update. For example, if you are using beforeUpdate, then the field has not been saved yet. After update it has. OnClick it may not be saved yet either....maybe it's the event your using?
in other words, consider the chain of events.
How are you updating the table, at this point? InsertInto query? Update query?
Could I see the code?
J'espere que je pourrai t'aider!

When the update is incorrect, it remains incorrect. I use the lost focus event of the order quantity control. Here is the code...

Maybe I am not using the right event... ??? Thank you very much for your help!:D



Private Sub QteCommande_LostFocus()
Dim rec As Recordset
Dim db As Database
Dim strSQL As String

If QteVente = 1 Then Total = PrixVente * QteCommande Else Total = PrixVente
If QteCommande = 0 Then
    Total = 0
    intResult = MsgBox("La quantité ne peut pas être 0", vbOKOnly)
Else

If QteCommande <> intQteCommande Then

If QteCommande <= Qté - QteVendus Then
    strSQL = "Select Status, StatutInit, QteVendus from MASTERSHOW where IdMaster =" & Modifiable12
    Set db = CurrentDb()
    Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
    rec.Edit
    rec!QteVendus = QteVendus + QteCommande - intQteCommande
    If rec!QteVendus = Qté Then
        rec!Status = 1
    Else
        rec!Status = rec!StatutInit
    End If

    rec.Update
    rec.Close
    End If
End If
End If
End Sub


Private Sub CtlDelete_Click()
On Error GoTo Err_CtlDelete_Click
Dim rec As Recordset
Dim db As Database
Dim strSQL As String


If QteCommande <> 0 Then intQteCommande = QteCommande
QteCommande = 0

strSQL = "Select Status, StatutInit,QteVendus from MASTERSHOW where IdMaster =" & Modifiable12
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
rec.Edit
rec!QteVendus = QteVendus + QteCommande - intQteCommande
If rec!QteVendus = Qté Then
rec!Status = 1
Else
rec!Status = rec!StatutInit
End If
rec.Update
rec.Close

'supprimer l'enregistrement
'
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_CtlDelete_Click:
    Exit Sub

Err_CtlDelete_Click:
    MsgBox Err.Description
    Resume Exit_CtlDelete_Click

End Sub

you seem to be very competent with recordsets?
Chantal, I am sorry, I can't see offhand, why it works intermittently.
I'm not sure if, like I said before, the "chain of events" is not saving the record, prior to opening the recordset. Maybe at the top of the code, save the record DoCmd.Save or Me.Refresh or Me.Requery..., then continue, to ensure that it is part of the following recordset. Or, change the event entirely, or like you said, move the procedure to the "confirm"button.
At this point, I can only assume.
I guess you haven't noticed a pattern when it doesn't work. If you leave QteCommande and immediately go to cmdConfirm, then maybe not?
Hasn't had time to save.
Outside of that Chantal, maybe the criteria isn't always met accordingly. Maybe it's in the math itself?
I see you have a Global Variable "intQteCommande", could that be changing unexpectantly? Maybe set it "again?" in the LostFocus procedure itself.
Chantal, it appears you've done very well with your recordsets, & the fact that they work MOST of the time, shows the problem's probably not there...(I always thought, one needed a semicolon, at the end of a SQL statement ...where IdMaster =" & Modifiable12 & ";" ...I guess not?

I'm sorry, I wish I could be more helpfull, but I can only speculate at this point.

Ton code apparait très bien, le problem ne doit pas être la?
Bonne chance!