Error if not selected

[Infinite]

Hello all! I have a:

List box named: lstEvents
and 2 forms
Main 1: frmEvents
2nd 1: frmEventsEdit1


I have the codeing from here
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

Now, the problem im having is, that for the shows I have this code:

ShowNames: ([ShowName] & " " & Format([StartDate],"mm-yyyy"))

That is so that each show has the month and year attached to the name, so its easier to find out when they were, from just looking at them in the list box.

But, if I use the search that John (The OP of that link) then if I were to search for 2015, and try to open a show, it just opens to a blank frmEventsEdit1. Even though I currently just tell it:

DoCmd.OpenForm "frmEventsEdit1", , , "EventID = " & Me.lstShows

I would like two things.

Either, to figure out why that isn't working, or 2, to get this If sentence im working on that goes like this:

If "EventID" = " " Then MsgBox "Error, please try again" Else DoCmd.OpenForm "frmEventsEdit1", , , "EventID = " & Me.lstShows

Either of those im fine with. Though I would prefer the first option more, I will be plenty thankful for either of them.

Thanks!

 

[davestuart]

Hi, I think you need to bind the lstEvents with the EventID. I don't think you intended this to be the EventID? ([ShowName] & " " & Format([StartDate],"mm-yyyy")), which is what I think is happening. Your Listbox box need to be bound to the EventID.

i.e. your listbox rowsource should be EventID: ([ShowName] & " " & Format([StartDate],"mm-yyyy")). In the Column width enter a 0 so that the EventID is hidden and only the ShowName and Date are shown. This listbox must be bound to column 1, EventID.

Hope this helps
Dave

 

[Infinite]

As you can see in the image (and thats a image of the list box, by the way) I have Event ID and ShowNames. What im unsure of, is why is it not working, when it should still Filter frmEventsEdit1 by the Event ID of the show, even if the show name is different.

 

[davestuart]

2 things.
1. Are you sure that the EventID is the bound Column in the listbox?
2. Do you have single select only on the list box or are you using multi-select?

I think the multi- select might be an issue. If you use a break point, can you see what the Event ID is? Finally, are you sure the EventID is an actual field name in the final form you are trying to filter? The field name must be in the data source of the form and it must be an exact match like EventID and not [Event ID]. Last point is maybe you need to pass in the EventID as Cint(EventID), being explicit with the data type.

Oh an the very last point is MAYBE you need to use Me.lstShow.Column(0)?

Hope these ideas help.
Dave

 

[Infinite]

Me.lstShow.Column(0)?

How would I use that?

DoCmd.OpenForm "frmEventsEdit1", , , "Me.lstShow.Column(0)= " & Me.lstShows

Something like that?

 

[davestuart]

Like this.
DoCmd.OpenForm "frmEventsEdit1", , , "EventID = " & Me.lstShows.Column(0)

I have attached a sample. Open the form and click on list box then take a look at the code. This is how you need to reference the listbox.

I don't know how you are doing this but this should help.

Good Luck
Dave

 

[Infinite]

The problem im having is, that, if I search for shows that end or contain 2015, if the dont start with it, they still open to a blank sheet. Which is why, if what I would like to fix it doesnt work, ill take the if sentence.


Other wise, it works the same as my code. But, what I need still doesnt work, and that isn't good.

Are you sure that the EventID is the bound Column in the listbox?

No, I am not. How would I do that?

Do you have single select only on the list box or are you using multi-select?

Can you explain that a little more? Not sure what you mean.

Finally, are you sure the EventID is an actual field name in the final form you are trying to filter?

I am.

I think the multi- select might be an issue. If you use a break point, can you see what the Event ID is?

Once again, I am unsure of what you mean.

 

[davestuart]

It's difficult to troubleshoot with your application without seeing it. Maybe zip it up and upload to your skydrive so I can take a look.

 

[Infinite]

In the main form (frmEvents) you can see all of my code and such. Just type 2015 in the search. If you can get it to open the shows up to the correct one (with 2015 in the search) and it does what I think it should, I would be VERY thankful

 

[davestuart]

Thanks. I'll review tomorrow. Just heading out for the evening.

 

[davestuart]

Hi, so the problem was that you needed * on BOTH sides of the Criteria like this. Like "*" & [Forms]![frmEvents]![txtSearchS] & "*", not just on the right side like you had. I created a query called qryEventEdit which is used in the frmEventsEdit1 and frmEventsEdit. Note you can just use one of them now. I also prefer queries to built in SQL for a forms data source. It makes things much cleaner.

I also removed the Record Selector as I hate those. The long strip on the left. Final bit of advice. Indent your code as it makes it easier to read.

i.e.
If a= 4 then
do this
Else
Do this
End If

No code should touch the left column except for
Public Sub A
You code should always be indented 4 spaces.
End Sub

Best of Luck
Dave

 

[Infinite]

Okay, I checked it out, and first, Thanks a TON man! I would NEVER have guessed looking there I would have kept just checking the one forum. 2nd, I still am not sure completely what you did

Im not sure were the code should go. Once I get that fixed, a LONG time error should be done Thanks!

 

[davestuart]

You're welcome!

Just look in the qryEventEdit query. That's where the criteria is. Bottom line really is that you don't need that criteria as you are already filtering for the EventID on the Docmd.OpenForm line.

Dave

 

[Infinite]

Ok, so the problem was that the main query had the correct Like, but the query that opened upon double click was the wrong one. I would have truly never thought of that! Thanks once again!