Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Unanswered: Want to open dynamic form based on 2 combo boxes

    I have 2 combo boxes that are pulling lists from 2 different tables. 1 table lists all current order numbers. 1 combobox lists Forms that are available. When someone chooses an order number and a form, I want it to pull up the chosen form with the info from the chosen orders record. All info for the record is in 1 table. Thanks for the help.

    Kevin

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Something like this should work for you:

    strForm=Me.cboForm

    Docmd.OpenForm strForm,OpenArg:=Me.cboOrder

    In the on open event of the forms you would capture the open arg property and react by doing something to limit the recordsource.

    HTH

  3. #3
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Thanks, but I need a little more help,....

    ok, but what kind of argument would capture that? and since currentdb.execute won't do select queries, how can i select the record info based on that field that I have captured? Thanks,

    Kevin

    Originally posted by dynamictiger
    Something like this should work for you:

    strForm=Me.cboForm

    Docmd.OpenForm strForm,OpenArg:=Me.cboOrder

    In the on open event of the forms you would capture the open arg property and react by doing something to limit the recordsource.

    HTH

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    You could feed openarg into:

    Filter
    Find
    SQL
    Recordset

    Any of these would find the record you want take your pick.

    HTH

  5. #5
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    cool, things seem to be almost working

    I am so close i can taste this one...i've got both the correct form opening and i believe I have the OpenArgs va,ue correct according toi the debugging I did. I am using an sql statement to select the record and it looks something like this: select top 1 missionroster.* from missionroster where [missionroster.combined]=[me]![openargs];

    When I select the form and it pulls the form up, it asks me for the me!openargs value, which obviously means i didn't retrieve the variable correctly, I'm real new to this, so I appreciate your help and patience.

    Kevin

    Originally posted by dynamictiger
    You could feed openarg into:

    Filter
    Find
    SQL
    Recordset

    Any of these would find the record you want take your pick.

    HTH

  6. #6
    Join Date
    Feb 2002
    Posts
    403
    select top 1 missionroster.* from missionroster where [missionroster.combined]=[me]![openargs];
    select top 1 missionroster.* from missionroster where [missionroster.combined]=" & Me.Openargs & ";"

  7. #7
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Thanks, Everything works great!

    I appreciate all the help, you really saved me on this one.

    Kevin

    Originally posted by dynamictiger
    select top 1 missionroster.* from missionroster where [missionroster.combined]=" & Me.Openargs & ";"

  8. #8
    Join Date
    May 2002
    Posts
    395

    error occurred in the openarg of openform method

    Originally posted by dynamictiger
    Something like this should work for you:

    strForm=Me.cboForm

    Docmd.OpenForm strForm,OpenArg:=Me.cboOrder

    In the on open event of the forms you would capture the open arg property and react by doing something to limit the recordsource.

    HTH
    Hi,
    You posted a solution using openform method and openArg as argument. I am trying to apply them in my event procedure the same way but I received "Named argument not found" in the OpenArg:=
    I can't see why the error occurred. Could you please help me out?

    Thank you much!1

    Private Sub w_o__history_Click()

    On Error GoTo Err_w_o_history_Click

    Dim stDocName As String
    Dim work_ord_num_lookup As String

    stDocName = "shipment_history_list"
    work_ord_num_lookup = Me!shipping_sched_list_subform.Form!work_ord_num.V alue

    ''Named argument not found" ERROR OCCURRED in the OpenArg:=

    DoCmd.OpenForm FormName:=stDocName, OpenArg:=work_ord_num_lookup

    Exit_w_o_history_Click:

    Set check_wo_history = Nothing
    Exit Sub


    Err_w_o_history_Click:

    MsgBox Err.Description
    Resume Exit_w_o_history_Click

    End Sub

  9. #9
    Join Date
    Feb 2002
    Posts
    403
    work_ord_num_lookup = Me!shipping_sched_list_subform.Form!
    Refers to a form, not a value. Try something like:

    Me.controlname

    In fact this line could look like

    DoCmd.OpenForm FormName:=stDocName, OpenArg:=me.controlname

    Where controlname is a value you want to pass to the form you are opening.

    If the control is indeed a subform control you are referencing from a main form the syntax should be:

    Forms("MainFormName").Form("SubFormName").Form("co ntrolname")

    Where subformname is the name as it appears in the properties dialog when viewed from the mainform.

    HTH

  10. #10
    Join Date
    May 2002
    Posts
    395

    find a value in a field of a form

    Originally posted by dynamictiger
    Refers to a form, not a value. Try something like:

    Me.controlname

    In fact this line could look like

    DoCmd.OpenForm FormName:=stDocName, OpenArg:=me.controlname

    Where controlname is a value you want to pass to the form you are opening.

    If the control is indeed a subform control you are referencing from a main form the syntax should be:

    Forms("MainFormName").Form("SubFormName").Form("co ntrolname")




    Thanks for your reply. That part is working now.
    I do need help in getting a value from a field in a form.
    What I am trying to do is when a work order is selected @ the subform and the user clicks on a button a history form opens up with the records for that work order. If the no previous records found for the order a message pops up.

    The history form is bound to W_O_History table.
    I’m having trouble to get the If statemtent working. I am trying to check if there is a value in work_ord_num column in the history form that matches the value passed in from the subform.

    “If work_ord_lookup = Me!work_ord_num Then”

    What it is doing is comparing the value from the subform with the previous filtered value in the history form.

    The history form is bound to W_O_History table.
    I'm using in Access FE and SQL Serving as BE.
    ·
    Private Sub Form_Open(cancel As Integer)
    Dim work_ord_lookup As String
    Me.ServerFilterByForm = False 'clear all filter criteria in the Server Filter By Form window

    If Not IsNull(Me.OpenArgs) Then
    work_ord_lookup = Me.OpenArgs
    Else
    Exit Sub
    End If

    ‘’’’’’’’’’problem here. Need to fix the code or something’’’’’’’’’’’’’’’’’’
    '''work_ord_lookup is the value from the subform
    '''Me!work_ord_num is a field on the history form

    If work_ord_lookup = Me!work_ord_num Then

    Me.ServerFilter = "work_ord_num = '" & work_ord_lookup & "'"
    DoCmd.ApplyFilter , "work_ord_num = '" & work_ord_lookup & "'"
    Else
    MsgBox "There are no shipments found for Work Order Number: " & work_ord_num & " ", vbExclamation
    End If

    End Sub

    Thank you!!

    Where subformname is the name as it appears in the properties dialog when viewed from the mainform.

    HTH

  11. #11
    Join Date
    Feb 2002
    Posts
    403
    I think your open args carries the value you want the form to open with. That being the case:

    If len(Me.OpenArgs)>0 then

    strSQL="SELECT * FROM tblYourTable WHERE YourField = " & Me.OpenArgs

    Me.recordsource=strSQL

    End if

    Should select the correct record

  12. #12
    Join Date
    May 2002
    Posts
    395

    pass in openargs to open form procedure

    Good Morning,
    I have two sub procedures calls form_open event and pass in openargs to the procedure.
    The openargs are work_ord_lookup and shipped_dates. They are two fields/columns in the same table bound to the same form. Depending on the openargs passed in I need the form to open and filter by that value.
    I believe I’ll need the variable name of the openargs instead of the value so it will know which filter to execute in IF statement, but I’m not sure how I can get it.
    This is what I like to do but it is not working. There is no error appeared but the if statements is NOT being executed. I don’t think is syntax but it doesn’t recognize the If Or ElseIf statements.
    I am using Access 2000 and SQL server BE.

    Your help is greatly appreciated.
    Thank you.

    Private Sub Form_Open(cancel As Integer)

    Dim work_ord_lookup As String
    Dim bdt As Date
    Dim bdate As String
    Dim blankpos As Integer

    If Me.OpenArgs = "work_ord_lookup" Then
    work_ord_lookup = Me.OpenArgs
    DoCmd.ApplyFilter , "work_ord_num = '" & work_ord_lookup & "'"

    ElseIf Me.OpenArgs = "shipped_dates" Then
    bdate = Me.OpenArgs
    blankpos = InStr(1, bdate, " ")
    bdt = Left$(bdate, blankpos - 1)
    DoCmd.ApplyFilter , "[shipped_date] > '" & bdt & "'" 'and " & '" & edt & "'

    End If

    End Sub

    ////

    Private Sub w_o__history_Click()
    Dim work_ord_lookup As String

    On Error GoTo Err_w_o_history_Click


    Dim stDocName As String
    stDocName = "shipment_history_list"

    work_ord_lookup = Me!shipping_sched_list_subform.Form!work_ord_num.V alue '' & " " & Me!shipping_sched_list_subform.Form!work_ord_line_ num.Value
    DoCmd.OpenForm FormName:=stDocName, OpenArgs:=work_ord_lookup



    Exit_w_o_history_Click:

    'Set check_wo_history = Nothing
    Exit Sub

    Err_w_o_history_Click:
    MsgBox Err.Description
    Resume Exit_w_o_history_Click

    End Sub

    /////

    Private Sub Preview_Click()
    Dim bdt As Date
    Dim edt As Date
    Dim shipped_dates As String

    bdt = Me![Beginning Entry Date]
    edt = Me![Ending Entry Date]

    If IsNull([Beginning Entry Date]) Or IsNull([Ending Entry Date]) Then
    MsgBox "You must enter both beginning and ending dates."
    DoCmd.GoToControl "Beginning Entry Date"
    Else
    If [Beginning Entry Date] > [Ending Entry Date] Then
    MsgBox "Ending date must be greater than Beginning date."
    DoCmd.GoToControl "Beginning Entry Date"

    End If
    End If
    shipped_dates = bdt & " " & edt
    '"
    DoCmd.OpenForm "shipment_history_list", OpenArgs:=shipped_dates

    End Sub

  13. #13
    Join Date
    Feb 2002
    Posts
    403
    I see what is happening here

    You are passing in the words "work_ord_lookup" as the argument in the openArgs and then telling variable work_ord_lookup = Me.OpenArgs, which wiwll make your variable work_ord-lookup ="work_ord_lookup".

    A suggestion might be:

    Dim frm as Form

    set frm = new form

    frm=Forms("NameofCallingForm").Form

    If Me.OpenArgs = "work" Then
    work_ord_lookup = frm("NameofControl")
    DoCmd.ApplyFilter , "work_ord_num = '" & work_ord_lookup & "'"

    ElseIf Me.OpenArgs = "shipped" Then
    bdate = frm("NameofControl")
    'blankpos = InStr(1, bdate, " ")
    'bdt = Left$(bdate, blankpos - 1)
    DoCmd.ApplyFilter , "[shipped_date] > '" & bdt & "'" 'and " & '" & edt & "'

Posting Permissions

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