Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 11th, 2004, 09:31 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default Need help with recordsets

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

Reply With Quote
  #2 (permalink)  
Old June 17th, 2004, 12:34 AM
Authorized User
 
Join Date: Oct 2003
Location: Toronto, Ontario, Canada.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!




Reply With Quote
  #3 (permalink)  
Old June 18th, 2004, 10:25 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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!

Reply With Quote
  #4 (permalink)  
Old June 18th, 2004, 06:27 PM
Authorized User
 
Join Date: Oct 2003
Location: Toronto, Ontario, Canada.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!


Reply With Quote
  #5 (permalink)  
Old June 21st, 2004, 02:53 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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


Reply With Quote
  #6 (permalink)  
Old June 21st, 2004, 11:58 PM
Authorized User
 
Join Date: Oct 2003
Location: Toronto, Ontario, Canada.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!





Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordsets JezLisle Access VBA 11 July 17th, 2007 03:47 AM
Help with Recordsets voskoue Access VBA 1 January 23rd, 2007 08:36 AM
Assigning Recordsets mrjeret BOOK: Access 2003 VBA Programmer's Reference 0 July 6th, 2006 09:39 AM
Cloned Recordsets taraj Access VBA 4 June 13th, 2006 08:28 AM
Recordsets bph Access VBA 17 February 17th, 2004 03:19 PM



All times are GMT -4. The time now is 04:54 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.