Selecting a value

[Danryan]

I am having a problem with a listbox.
After updating a combo box I want it to immediately
select a value from a listbox. This is all on the same
form and it is necessary so I can run a query.
Can anybody help please?

 

[dcx693]

What value do you want to select from the listbox? One that matches something from the combo box?

 

[Danryan]

No, just the first value will do

 

[Mile-O]

Should be:

Me.ListBox = Me.ListBox.ItemDate(0)

 

[dcx693]

Should be:

Me.ListBox = Me.ListBox.ItemDate(0)

ItemData(0)

 

[Mile-O]

must be a typing niggle I have - seem to always say Date over Data.

 

[Danryan]

Reply

I'm afraid that doesn't work. Here is my code:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.findfirst "[name]='" & Me![Combo1] & "'"
Me.Bookmark = rs.Bookmark
Me.lst.RowSource = "SELECT dead.[box] FROM dead WHERE (((dead.[name])=[Forms]![Dead]![Combo1]));"
Me.lst.Visible = True

The query is run when the user clicks on the listbox, however I want it so if the listbox only contains 1 entry, the user does not have to click on the box to find out if it is missing.

If I add the query coding from the on click below this the following error message appears: "the value you entered isn't valid for this field". Below is the query code:
Me.true = DLookup("[Query1].[missing]", "[query1]")
If Me.true = False Then
Me.Missing_Box.Visible = False
GoTo fin
Else
Me.Missing_Box.Visible = True
Me.Missing_Box = "MISSING"
End If
fin:
Exit Sub

 

[Mile-O]

Well, I changed all your code, fixed a few things, but you have issues with naming objects after Access's Reserved words (problematic if you don't know how to deal with them).

The Recordset Object (less ambiguous) is DAO so you may find that you have to declare a reference to that library if ADO is your current standard.

Name and True are not advised names for fields or objects.

Fixed the RowSource statement to equal the value in the combo and not the literal string.

Stopped any possible errors whereby the name field may have an apostrophe.

You're asking for trouble, though, calling anything True.

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    
    rs.FindFirst "[name]=""" & Me.[Combo1] & """"
    Me.Bookmark = rs.Bookmark
    Me.lst.RowSource = "SELECT dead.[box] FROM dead WHERE (((dead.[name])= " & Forms("Dead").[Combo1] & "));"
    Me.lst.Visible = True
    
    Me.True = DLookup("[missing]", "[query1]")
    If Me.True = False Then
        Me.[Missing_Box].Visible = False
        Exit Sub
    Else
        Me.[Missing_Box].Visible = True
        Me.[Missing_Box] = "MISSING"
    End If

Exit Sub

 

[Danryan]

Problem

Hi, thanks for the code but slight problem, error message:

User-defined type not defined
rs As DAO.Recordset

Do you know what this means?

 

[Mile-O]

Look in the FAQ forum at the top of the board.

It's because you are using Access 2000 or above and the RecordSetClone is a DAO property.

 

[Danryan]

Thanks for your help on that, I've sorted that part but
when I choose a value from my combo the following error appears:
Run-time error '-2147352567 (80020009)':
The value you entered isn't valid for this field.
Me.true = DLookup("[missing]", "[query1]") - line highlighted

Is it because this is a true/false field?

 

[Mile-O]

Can you post an example of this code - removing any personal data?

To help further I need to visualise it; I did say, though, that calling anything True was asking for trouble.

 

[Danryan]

I'll change the name from true to tr if that helps

 

[Danryan]

Changing the name didnt affect it, I tried to attach a copy of the database zipped, but it is too big.
The code you sent me last is the one I am using.

 

[Mile-O]

chkMissing would be (not is) more appropriate.

Naming conventions are excellent at a glance indicators of an object, variable, etc

 

[Danryan]

Database

I've managed to shrink it!

 

[Mile-O]

Just a copy of the database stripped of all the irrelevant stuff - just the form and anything used in it (table, query, macro, subform, etc) then compact it and .zip it.

 

[Danryan]

Hi Mile-o-phile,
have you had a chance to look at the database I sent yet?