Need help wit the "DoCmd.OpenForm"

[IamKJVonly]

I have a from that has a list box in it. But when I click on a line in the list nothing happens:

The form name that has the list in it is: frmListByDate
The list Name in that form is: Book_List
The Form I want to go to is:

frm_C_BookByDateAllFields
Record Id is: C_BookID (caption is BookID)

Here is the code I am using. (I got it from another thread in this forum but I don't remember where)



Private Sub BOOK_LIST_Click()
DoCmd.OpenForm "frm_C_BookByDateAllFields", , , "C_BookID = " & Me.BOOK_LIST
End Sub

When I click on a record in the list the line is highlighted but nothing else happens.

Any idea what I can do to make this work?
Any help will be greatly appreciated.
Dave

[June7]

The VBA code line is highlighted? Surely there is an error message?

[CJ_London]

in addition to the error message, you also need to tell us more about your listbox - rowsource, bound column - also what datatype bookid is? text or number?

[IamKJVonly]

I did not see an error message: or do not know how to find it. Like I said the line in the list box I clicked on is highlighted but that is all that happens.

the rowsource is: SELECT [qry_C_ListboxByDate].C_DateLastRead, [qry_C_ListboxByDate].C_Location, [qry_C_ListboxByDate].C_BookName, [qry_C_ListboxByDate].C_BookID FROM qry_C_ListboxByDate;

Bookid is a number

I don't think anything is bound because the form's design view says the list box is unbound

If you need anything else let me know

maybe the way I'm loading the list is wrong???

[Missinglinq]

I believe the OP means the line in the Listbox...not a line in code.

Which column of the Listbox is the Bound Column? (Don't confuse this with the Listbox itself being Bound) C_BookID is the last Column, per your posted SQL Statement, and it would have to be the Bound Column in order for your code to work...and would not be so by default...I believe you'd have to manually change that.

Linq ;0)>

[IamKJVonly]

How do I change the C_BookID column to bound. Sorry I'm just use to access. But what you are saying makes sense.
Dave

[davegri]

Change your listbox rowsource to this:
SELECT C_BookID, C_DateLastRead, C_Location, C_BookName FROM qry_C_ListboxByDate;
Change the Bound Column to 1. Properties, Data tab
Change the Column Count to 4. Properties, Format tab
Change the Column Widths to 0;1;1;1. This will make BookID invisible and make lastread, location,and bookname all 1 inch wide in the listbox

[June7]

There is a procedure which is presumably associated with the listbox. Something should happen, even if it is an error.

[CJ_London]

all 1 inch wide

or one cm depending on windows settings - adjust to suit

[IamKJVonly]

Well Thanks to you guys some progress has been made. The DoComm still does NOT work.

BUT I created a macro (Not imbedded) and it works like a champ. the only thing I don't understand is that the macro's where command looks a lot different than I expected itto

It says: "[C_BookID]=[Forms]![frmListByDate]![BOOK_LIST]"

I don'tknow how to paste this into the "DoCom" to see it if this different coding will work. Ps this macro does not depend on a Bound C_BookID.

So now I'm hopping someone can figure out how to plug the Macro's code into the DoCom command

Things are looking better.
Dave

[davegri]

Have a look at this working example:

IamKJVonly-davegri-v01.zip

[IamKJVonly]

davegri

I agree that the code should work but it doesn't. I'm going to take a break and rethink what I'm doing wrong with the DoCom command .
Sometimes I have a hard time getting access to take changes like this. Access says it does but doesn't. A rebuild will usually solve that problem.
Thanks for the help
Dave

[CJ_London]

in your button properties do you have '[Event Procedure]' against the click property?

So now I'm hopping someone can figure out how to plug the Macro's code into the DoCom command

there is an option on the ribbon to convert macros to vba code. In form design view, go to the design tab - you will see the option to the right

[davegri]

I agree that the code should work but it doesn't.

If you're talking about the DB that I submitted, it does work for me.
I'm thinking maybe you downloaded my DB into a not trusted location.

[IamKJVonly]

Ajax It was a good idea to convert the macro to vba It was turn into a function:

Function cmdTestListClick()
DoCmd.OpenForm "frm_C_BookByDateAllfields_B", acNormal, "", "[C_BookID]=[Forms]![frm_C_BookByDateAllfields_B]![C_BookID]", , acNormal
End Function

It did not work. thanks for trying.

Davegri
I don't know what a trusted location is I use windows explorer and win zip to unpack it.
No go.

Like I said I'm taking a break and have decided I can live with the macro.
I'm hoping when I rebuild the project form the ground up it will work.
Again thanks for the help it was all useful but at this point I'm betting on the rebuild.
Dave