Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Can't find field/form (Access 2003 SP3)

    Edited by HansV to replace zip file with .png version of the bitmap it contained.

    Following is some code that I can't figure out why it doesn't work. The attached message I can't seem to get around.

    Private Sub cmdInsertassmt_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String, strSQLInsert As String

    'strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE AssortmentID = '" & Me!sbfInternationalAssortments.txtAssortmentID & "'"
    strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE AssortmentID = '" & Me!sbfInternationalOrderDetails.Form!sbfInternatio nalAssortments.Form!txtAssortmentID & "'"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.EOF = False Or rst.BOF = False Then 'This line just checks to make sure there is at least one record in the recordset
    rst.MoveFirst 'move to the first record. Hopefully there is only one record but if there happens to be more than one for some reason it will simply use the first one
    While rst.EOF = False
    strSQLInsert = "INSERT INTO [tblInternationalOrderDetails] (OrderID, ProductID, Quantity) SELECT " & Me!OrderID & ", " & rst!ProductID & ", " & rst!Quantity * Me.txtDefaultQty & ""
    'MsgBox strSQLInsert 'use this to check your SQL statement before executing it
    CurrentDb.Execute strSQLInsert, dbFailOnError
    rst.MoveNext
    Wend


    End If


    Set dbs = Nothing
    Set rst = Nothing
    Me!sbfInternationalOrderDetails.Requery
    Me!sbfInternationalOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sbfInternationalTotals.Requery
    End Sub
    Attached Images Attached Images
    • File Type: png x.png (18.2 KB, 0 views)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    When referring to a subform, you must use the name of the subform as a control on the form that contains the subform. This is not necessarily the same as the name of the subform in the database window.
    To see the control name, open the form that contains the subform in design view, and click once on the subform to select it. Don't click on it a second time, for that would select something in the subform.
    When you have selected the subform, the Objects box on the toolbar and the title bar of the Properties window will display the control name.

    Also see this handy reference: Refer to Form and Subform properties and controls.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    Thanks, HansV. That fixed it.
    Now my problem is, the frmInternationalAssortments has several assortments in it. My code is only inserting the first assortment. What's wrong?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    The code only looks at the current record in the subform - if you haven't explicitly selected another one, that'll be the first record. If you want to act on all records in the subform, you'll have to loop through a clone of the recordset of the subform (you can use .RecordsetClone for this), and execute the code for each record.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    OK, that makes some sense to me, but I don't know much about DAO. How do I make a recordsetclone? Is that something I can easily add to the current code?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    Try the following, modifying the reference to the subform:

    Private Sub cmdInsertassmt_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
    Dim strSQLInsert As String

    Set dbs = CurrentDb
    ' Modify as needed
    Set rst2 = Me.frmInternationalAssortments.Form.RecordsetClone

    Do While Not rst2.EOF
    strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE " & _
    "AssortmentID = '" & rst2!txtAssortmentID & "'"
    Set rst = dbs.OpenRecordset(strSQL)

    Do While Not rst.EOF = False
    strSQLInsert = "INSERT INTO <!t>[tblInternationalOrderDetails]<!/t> " & _
    "(OrderID, ProductID, Quantity) SELECT " & Me!OrderID & ", " & _
    rst!ProductID & ", " & rst!Quantity * Me.txtDefaultQty
    CurrentDb.Execute strSQLInsert, dbFailOnError
    rst.MoveNext
    Loop
    rst.Close
    rst2.MoveNext
    Loop

    Set rst = Nothing
    Set rst2 = Nothing
    Set dbs = Nothing
    Me!sbfInternationalOrderDetails.Requery
    Me!sbfInternationalOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sbfInternationalTotals.Requery
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    I'll work with that tomorrow or next week. I have to leave work now. Thanks for your time.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    OK, I put the mentioned code in and now when I click on the command button nothing happens.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    I think we'll have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    I must leave for a family Christmas get-together. Thanks for your help. I was working on getting the database on here, but even after converting to 97 I can't get it under 100kb. Merry Christmas!

  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    OK. Back to work for a while today. I finally got my database small enough to post. Here it is.

    P.S. Why is 100K the maximum size?
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    You omitted the line

    Set rst = dbs.OpenRecordset(strSQL)

    above the line

    Do While Not rst.EOF = False

    so the recordset rst is never opened.

    We have a limit of 100 KB because we don't want to be swamped with multi-megabyte attachments. They would take up too much space on the Lounge server, and they would have a seriously negative effect on performance and load time of pages.

  13. #13
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    OK, this is what I'm getting now.

    How do you insert small pictures right onto the screen?
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't find field/form (Access 2003 SP3)

    Please read my previous reply more carefully, it mentions where you should insert the omitted line. You inserted it in the wrong place.

    You can attach a picture (.gif, .jpg, .png) directly to a post, but only one at a time. So if you have two screenshots, you need to combine them into one picture, as I have done in the attachment below (I copied the error message and pasted it into the picture of the code).
    Attached Images Attached Images

  15. #15
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Can't find field/form (Access 2003 SP3)

    HansV, I don't know what I was doing wrong. I carefully scrutinized my code and was quite sure I was putting that where you said, but it didn't work. I am inserting here what I finally settled with so others can see it and hopefully it can help someone else in like trouble.

    Private Sub cmdInsertassmt_Click()

    Dim dbs As DAO.Database
    Dim rstForm As DAO.Recordset
    Dim rstData As DAO.Recordset
    Dim strSQL As String

    Set dbs = CurrentDb

    ' We need to look at the forms records, so get a reference to
    ' its recordset
    Set rstForm = Me!frmInternationalAssortments.Form.RecordsetClone

    ' Loop through the forms records.
    rstForm.MoveFirst
    Do Until rstForm.EOF
    ' Fetch data from tblAssortments based on the current form record.
    strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE AssortmentID = '" & rstForm![AssortmentID] & "'"
    Set rstData = dbs.OpenRecordset(strSQL)

    Do Until rstData.EOF
    ' and now append record using data in rstData
    strSQL = "INSERT INTO [tblInternationalOrderDetails] " & _
    "(OrderID, ProductID, Quantity) SELECT " & Me!OrderID & ", " & rstData!ProductID & ", " & rstData!Quantity * Me.txtDefaultQty & ""
    CurrentDb.Execute strSQL, dbFailOnError
    rstData.MoveNext
    Loop
    rstData.Close
    Set rstData = Nothing

    ' Move to next form record
    rstForm.MoveNext
    Loop

    Set rstForm = Nothing
    Set dbs = Nothing

    Me!sbfInternationalOrderDetails.Requery
    Me!sbfInternationalOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sbfInternationalTotals.Requery
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •