Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling an Active X Combo Box from a query (Access 2000)

    A tougher one:


    Why does my form go blank?

    I have created forms where the date selections in a combo box are passed to a query with "Between...AND" criteria. The new wrinkle is that I am using an Active X calendar and the traditional criteria leaves my new form entirely blank.

    Here is my traditional Query Citeria:
    Between [Forms]![FormSearch]![StartDate] And [Forms]![FormSearch]![EndDate]

    I have one date in the query I want to parameter: Trade Date.

    The form has a StartDate and an EndDate date combo box with Active X Calendar controls so I can select dates from a calendar. The form works great- until I add the "Between...AND" query criteria above. When I remove the query criteria, my form appears normal- I just cant pass any values to the query. When I add the criteria, my form then appears blank. Here is the extra Form VBA:

    Enitre Form Code:
    Option Compare Database
    Option Explicit
    Dim Originator As ComboBox

    Private Sub Calendar_Updated(Code As Integer)

    End Sub

    Private Sub EndDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set Originator = EndDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(Originator) Then
    Calendar.Value = Originator.Value
    Else
    Calendar.Value = Date
    End If
    End Sub

    Private Sub RunQuery_Click()
    On Error GoTo Err_RunQuery_Click

    Dim stDocName As String

    stDocName = "QryAsOfCrewDescr"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_RunQuery_Click:
    Exit Sub

    Err_RunQuery_Click:
    MsgBox Err.Description
    Resume Exit_RunQuery_Click

    End Sub
    Private Sub Calendar_Click()
    Originator.Value = Calendar.Value
    Originator.SetFocus
    Calendar.Visible = False
    Set Originator = Nothing
    End Sub

    Private Sub StartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set Originator = StartDate
    Calendar.Visible = True
    Calendar.SetFocus
    If Not IsNull(Originator) Then
    Calendar.Value = Originator.Value
    Else
    Calendar.Value = Date
    End If
    End Sub

    What is different about passing this combo box to the query? I am not calling on a row source int he combo box properties, only asking the combo box to hold the value until I pass it. The Row Source Type is a value list, defined by the combo box wizard.

    Naturally, there appears to be something about the query. I admit I am really rusty and getting back into Acess, but I have other such queries that work, and nothing else seems to be different.

    Any ideas?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    Have you tried adding Value to the end of the expression? When you use just the name of the combo box, it assumes you are looking at the bound value, but your controls are presumably unbound. Not sure that will fix the issue, but it's worth a try. You might also need to define those as parameters in the query definition. The other solution you might try is actually assembling the SQL statement in VBA and looking at it with the de###### - that will often identify what is wrong with the SQL statement.
    Wendell

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    That didnt help. I think that any criteria entered into the query results in a blank form where I try to pass an active x control vlue back to the query. I did one in the past where I could pick up the pre-existing date from a table and change it with the active x calendar, but cant seem to begin with an unbound value and pass it back to the query. I do it with all the other fields, just not a date field with a calendar attached.

    There must be something about the query criteria that I havent encountered before. I guess Ill keep hunting.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    You should be able to define parameters for dates in a query by the parameters clause.
    If you have time why don't you post a compacted/zipped database taking out any sensitive data.

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

    Re: Calling an Active X Combo Box from a query (Access 2000)

    Your code works as expected in a test I did, so if possible, follow Patt's suggestion of posting a stripped down copy of the database. See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    Thanks, here is a stripped down version. I have tried a thousand things, but this is basically where it started. I believe the calendar was an 8.0 control.

  7. #7
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    Thanks!

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

    Re: Calling an Active X Combo Box from a query (Access 2000)

    The reason that your form is blank is that you bound it to the same query you want to open. When the form is opened, the dates are not filled in yet, hence there are no records satisfying the criteria, hence the form is blank. Your form shouldn't be bound, so clear its Record Source property.

  9. #9
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling an Active X Combo Box from a query (Access 2000)

    You have got to be kiddding- it works! But why doesnt that apply when I had non-ActiveX controls? I had a whole "unbound" form set up that was working unitl I added the Calendar. I thought the whole point was to allow the form to open to fill in data to query the tables! Did the Calendar control add the record source, or was it there since I created the form (the latter I think)? or is it because of the way I linked the Calendar to the existing Record Source (query) that jarbled it up?

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

    Re: Calling an Active X Combo Box from a query (Access 2000)

    It's hard for me to say, because you modified the posted version of the database in such a way that the query doesn't return any records even if you remove all criteria. But I think that the bound form works for you without the date criteria because all the other criteria use wildcards, so if the controls for UserID etc. are blank, ALL records are returned.

Posting Permissions

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