Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing a Form's property to a Query (2003)

    The startup form in the attached mdb is actually opened from another form that passes the OpenArgs argument to it.
    The txtFirstName combobox on the form has qrylkpInstructors as its row source. How can I pass the form's OpenArgs argument to the query?
    I've tried using a global function but it doesn't work.
    Attached Files Attached Files

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

    Re: Passing a Form's property to a Query (2003)

    Since you haven't included the other form, I don't have the slightest idea. If you would like us to help you, please provide all relevant items.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    I've attached the other form which is now the startup form. You should click the Dictators button on it to open the other form. Couldn't whittle it down to less than 100k so here it is.

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

    Re: Passing a Form's property to a Query (2003)

    Place a text box txtAdminID on fpopInstructorSelect and set its Visible property to No.
    Create an On Load event procedure for fpopInstructorSelect:
    <code>
    Private Sub Form_Load()
    Me.txtAdminID = Me.OpenArgs
    End Sub
    </code>
    Set the condition for the AdministratorID field in the query to
    <code>
    [Forms]![fpopInstructorSelect]![txtAdminID]
    </code>
    See attached version - only 14 KB zipped <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    Thank you Hans, my mdb shrinks from 480kB to 384kB by removing the standard VBA Module but it's nowhere near your 212kB, and I've decompiled and compacted it while yours isn't even compacted on close. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    Awwwwww....I'll keep these additional measures handy. Thank you again <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    How do you apply Standard autoformat to the tables?

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

    Re: Passing a Form's property to a Query (2003)

    Edited by HansV to correct mistake

    I did the following:
    - Turned off subdatasheets in the tables.
    - Removed superfluous indexes.
    - Applied the Standard autoformat to the forms (this removes the bitmap from the background).
    - Create a blank new database.
    - Turn of Track Name AutoCorrect Info (in the General tab of Tools | Options...)
    - Imported the database objects into the new database.
    - Decompiled and compacted.

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

    Re: Passing a Form's property to a Query (2003)

    Sorry, I meant forms instead of tables. I have corrected my previous reply.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    I've added a "New" button to the fpopInstructorSelect form which opens the frmInstructors form and passes its OpenArgs property value to it. If you fill out First Name and Last Name on the frmInstructors form and hit TAB to save the record, you're presented with a run-time error: "The data has been changed".
    How's so?
    Attached Files Attached Files

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

    Re: Passing a Form's property to a Query (2003)

    You can't change bound controls in the After Update event of a form - the record has already been saved, and modifying fields would fire the Before Update and After Update events again, causing an endless loop, so Access doesn't allow it.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    But...if I put it in the BeforeUpdate event,
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' If on a new record
    If Me.NewRecord Then
    ' Generate a new Instructor ID
    Me.InstructorID = _
    Nz(DMax("InstructorID", "tblInstructors"), 0) + 1
    Me.Administrator = Me.OpenArgs
    End If
    End Sub</pre>

    I still get a run-time Error

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

    Re: Passing a Form's property to a Query (2003)

    You can't add a record to two tables at once.
    You should rethink your design.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    Thank you Hans, do you have any suggestions?

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

    Re: Passing a Form's property to a Query (2003)

    I don't understand the purpose of your forms, so I can't provide specific help, but you should have separate forms for adding an instructor and adding a record to the junction table tblAdminInstr. The user should first create a new instructor, and only then add a record to tblAdminInstr.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a Form's property to a Query (2003)

    I've made it work by using
    <pre>Private Sub Form_AfterUpdate()
    Set cmd1 = New ADODB.Command
    With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "INSERT INTO tblAdminInstr(AdministratorID, InstructorID)" & _
    " VALUES (" & Me.OpenArgs & ", " & InstructorID & ")"
    .CommandType = adCmdText
    .Execute
    End With
    Set cmd1 = Nothing
    End Sub</pre>


    but how can I make sure the INSERT INTO command is executed only if a new record has been created and not, for example, if the frmInstructors has only been edited?
    Attached Files Attached Files

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
  •