List Box will not List a display of records on another form

[westfallbp]

I have a listbox which has 2 fields that are displayed based on a query. The query is based on a field called date. The other field is Date and is grouped by count.

The listbox displays the date correctly. I have the following event procedure for double click.

Private Sub List18_DblClick(Cancel As Integer)
Dim stDocName As String
Me.Visible = True

stDocName = "FAuctionInput"

DoCmd.OpenForm stDocName, acNormal, , "[Auction Date] = " &
[List18]
End Sub


So according to the code based on what I double clicked on the listbox it should open the form FauctionInput and display all records on the form that match.

I have tried bounding the listbox to 0 and 1 and it still didn't work. Also I don't get an error message either but it does open the form like its a new record. Before you ask the form doesnt open a form and automatically go to a new record and also Fauction input has [Auction Date] in there also. Any ideas how to resolve this would be greatly appreciated. I have been using this code for all sorts of databases and this is the first time it left me hanging. It may be that its not a primary key or I am missing something. Also willing to change the vba code if someone has another way of double clicking on a record in a listbox and returning the results for it. Side Note: if I go to the querry and enter [Enter date] it works as a perimeter query but I prefer to just double click on the listbox and displaying the form. Thanks

[Welshgasman]

Firstly, do not call fields/variables with reserved names.
You need to surround any date with # and in either mm/dd/yyyy or yyyy-mm-dd format.

I use this constant.

Option Compare Database
Option Explicit


'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

[westfallbp]

I looked at the table mine is set to medium date: 12 Nov 25 as an example, How would my vba statement reflect that? Also, Auction Date isnt a reverved name...Confusing but thanks.

[Welshgasman]

I looked at the table mine is set to medium date: 12 Nov 25 as an example, How would my vba statement reflect that? Also, Auction Date isnt a reverved name...Confusing but thanks.

The query is based on a field called date

Use that constant to format the date to that pattern.

Dates are just numbers really.

Do not use spaces in fieldnames either. Nothing wrong with AuctionDate and a caption of Auction Date

[westfallbp]

I am starting to get it. Would I paste Public Const strcJetDate = "\#mm\/dd\/yyyy\#" in the query or do I make a txt field and paste it there. I know I am starting to get it because my date is fomated dd-mmm-yyyy The listbox displays it that way too. Tried other methods as well also. I must have one serious case of stupidity. That code I first pasted thought worked for everying I wanted to view that record but its usually my primary key. I will keep plugging away at and I do appreciate your help.

[Welshgasman]

DoCmd.OpenForm stDocName, acNormal, , "[Auction Date] = " & Format(Me.List18,strcJetDate)

Put the Constant code at the top of a normal module
I have edited the code to show more.

[Micron]

I think part of your problem might be that you're regarding listbox columns as fields. They are not fields. A listbox has a recordsource property and one or more columns. That code looks like it was converted from a macro, but the last 2 lines before the End Sub don't make sense. Neither does the form opening line. Looks to me that what you should be doing is setting the recordsource of the listbox in the opening form but after it has been opened. Then either refresh it, or open it invisible, refresh, then make visible if you don't want it to show first without showing data (or the wrong data).

[madpiet]

Are you using the multi-select property of the listbox? If not, why are you not using a combobox instead? WAY easier to deal with, takes up a lot less space...