Replies

I have frmAccountsReceivable open. On it is a list box that has the SQL code and query view I posted. Clicking the list box bookmarks the appropriate record from the underlying table, in order that it may be updated and payments applied. The records that show on the form and that are in the listbox are from tblTimeSheet. I am trying to give the end user the option of inserting dates to sort/limit the number of records that show in the listbox, between the dates they choose. The boxes are undbound and named Startdate and enddate. Han’s suggestion was to enter the code to requery the listbox behind a control button. The requery would be based on the query that you and he are helping me to get right.

Leesha

Hi Pat, Happy Easter,

I cut and copied the code you sent (yes it is frmTimeSheet not tblTimeSheet) and I still get a question asking me to define the Startdate and enddate parameters. I entered the dates into their respective unbound textboxes on frmTimeSheet and then ran the query and got the error. Where should I go from here?

Thanks for your help,
Leesha

Morning Hans!

Here’s the code and I sent a view of the query design as I’m not sure if I put the info in the criteria line the way you wanted.

PARAMETERS [Form]![tblTimeSheet]![Startdate] DateTime, [Form]![tblTimeSheet]![Enddate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], [STARTDATE] AS Expr1, [ENDDATE] AS Expr2
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
WHERE ((([STARTDATE])=[Form]![frmTimeSheet]![STARTDATE]) AND (([ENDDATE])=[Form]![frmTimeSheet]![ENDATE]));

Leesha

OK, did as instructed and when I ran the query (after putting in the dates on the form) I got a message that said “invalid bracketing of name”. I didn’t put in brackets. The query did it itself. I took them out and it happened again???????????

Leesha

Tee hee, SO am I!! I wasn’t about to let this thing beat me, so one way or another I was gonna find a way to make it work even I died trying. Honestly, it was hard enough sending you my code to laugh at, to have to admit that I was thinking of this work around almost killed me!!! Whew!! St. Jude (patron St. of Hopeless Causes – as in my coding) must’ve been listen for your sake!

Thanks again. I’m now off to try the parameter stuff you sent me earlier but told me not to do until I took a break. I did take a break, actually ate and now I’m back at it again. Lets hope this goes easier. At least it isn’t a show stopper!

Leesha

I’m near tears with giddy relief. It works. Well, chalk it up to a major learning experience?!?!?!?!

Thank you!

Leesha

Oh God, that was at about the 8 hour mark when I was brain dead! I must’ve misunderstood. I’m glad you’re not here in the states as I can feel your hands around my neck. You’re either a very patient man or just put your fist through the wall. I’ll go try this AGAIN!

Humbly,
Leesha

Hans, I know its an unsatisfactory work around but I’ve got to come up with something that makes getting to these reports easier for the end user who presently is typing the name in when the query prompts her and if the name is spelled wrong etc. she gets nothing. Presently with the number thing going on its a useless form. I’m not even sure if the work around would do the trick but its all I could come up with during the few hours I stepped away from this. Honestly, I’d rather be coding in Excel. My brain thinks like Excel and I could work around this so much easier. This is driving me freaking nuts!

Leesha

Pat, the form is bound to tblAccountDemographics. The Listbox is bound to Account Name and two input boxes are unbound. Clicking the listbox bookmarks the name used to open the report according to the date range. The code is as follows:

Dim strWhere As String

strWhere = “[account Name] = ‘” & Me.List2.Column(1) & “‘” & _
” And ([Date] Between #” & Me.STARTDATE & “# And #” & Me.ENDDATE & “#)”

DoCmd.OpenReport “rptAccountInitial/CopyInvoice”, acViewPreview, , strWhere

The rowsource code for the table is as follows:

SELECT tblAccountDemographics.ID, tblAccountDemographics.[Account Name]
FROM tblAccountDemographics
WHERE (((tblAccountDemographics.[Account Name])<"private pay"))
ORDER BY tblAccountDemographics.[Account Name];

Any thoughts?

Leesha

Shoot!!!! I’m thinking the only way I’m gonna get around this is to just put in a bogus entry such as AAA in line one so that when the number gets applied it doesn’t affect the rest of the database. It’s lame I know but at least then the rest of it works. Any way to keep the first row from being seen in the listbox so I’m the only one who knows about the “bug”?

Thanks,
Leesha

THANK YOU for not commenting on the code (as you see I’m still at it). It took all I could do to send it! Keep in mind I’m a nurse and a self taught Access/Excel programmer and I get by. The code I sent you was for the issue with the parameters (I’ll try the suggestion from your post later). This code that I’m attaching is for the name change thing. This name change thing is a show stopper for this database. NOW please do not laugh or groan to hard.

Leesha

Cute emoticons! OK, I’ll try to leave it alone for a bit and then come back to it to try your suggestion. I’m literally brain dead at this point. This is a hobby for me (as I’m sure you can tell) so if I don’t get this done on non work time it’ll be forever before I get back to my friend.

Gonna eat I think too! Haven’t done that either!!

Leesha

I apologize if that was confusing. I thought separating out the two issues would make it easier. Attached is the code that is giving me the parameter issue when I try to limit the listbox to open between two sets of dates.

Thank you for your time,
Leesha

Thank you for the clarification. I’m bleary eyed for working on this for the past 10 hours. I must’ve misunderstood.

I have a form called frmTimeSheet and a table called tblTimeSheet. The form “frmTimeSheet” is open and that is the one that I have the unbound listbox and unbound text boxes. When I changed the name to frmTimeSheet I still get the parameter questions and for the heck of it I tried tblTimeSheet and got the same error.

Leesha

This is to the thread regarding numbers replacing Account Names when opening a report from a listbox. I posted it separately when you said you weren’t sure what was hope in the hopes that someone else had had this issue. I was trying to keep them separate.

Leesha