Database replacing data on own (2000)

Topic

New Reply

I have a form with a list box whose control source is tblAccountDemographics. The row source 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];

The control source is Account Name. I am using the list box to open a report for the name clicked in the list box. In addition, there are two nonbound input boxes used for "Startdate" and "Enddate" to give a range of dates for the report. The code to open the report is behind a command button and 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

My problem is that when the form opens and is used it runs fine. No problems. Once the form is closed and reopened again, the first name/row in the table that gets changed from the Account Name to an arbitrary number, not necessarily the ID number that corresponds with the name being changed. Please note, this does not happen until after the form has been closed and reopenned.

To make matters worse, it changes the data in the underlying table called tbltimesheets that uses this table as a lookback table.

Any suggestions would be greatly appreciated.

Leesha

Reply | Quote

Replies

Ummm, is your list box bound to a field on the form? It should be unbound, otherwise selecting a name in the list box will change the bound field to the ID corresponding to the selected name.

Reply | Quote

Sigh, no it isn’t. I even tried taking the SQL statement out of the rowsource property to see if that would make a difference and to see if only using the command button to fill the listbox would help, however I get the same parameter question.

Leesha

Reply | Quote

To which thread are you replying now?

Reply | Quote

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

Reply | Quote

But you complained about getting a parameter question – I thought that was in the other thread, and that this one was about data getting changed spontaneously.

Anyway, I don’t know how this can happen. Perhaps, you can post the complete code behind the form. If it’s long, put it in a text file and attach that, otherwise the thread becomes hard to read (and slow to load for Loungers with dial-up connections).

Reply | Quote

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

Reply | Quote

I’ll refrain from commenting on the code in general. I see nothing that would modify a name field – the only fields whose values get modified in this code are Bill_Rate, Total_Billed, Unbillable_Private_Pay_Time and som others like those, not name fields. About getting prompted for parameters, see the other thread.

Reply | Quote

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

Reply | Quote

I don’t see anything in this code that would change a name field either…

Reply | Quote

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

Reply | Quote

That’s an extremely unsatisfactory workaround. You can add a condtion to the rows ource of the list box to exclude the bogus value (… And FieldName ‘AAA’ “, where FieldName is the name of the field containing ‘AAA’), but are you sure it will help? Perhaps the first displayed value will be changed…

Reply | Quote

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

Reply | Quote

Is the form that holds the Account Code and StartDate and EndDate bound or unbound?

Is the Account Code field bound or unbound?

Pat

Reply | Quote

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

Reply | Quote

Hey! I asked you in post 245380 whether the list box was bound and in post 245384 you replied that it wasn’t!
If it’s bound to Account Name, that’s the reason that the name gets overwritten with an ID. The list box must be unbound, i.e. the Control Source must be empty.

Reply | Quote

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

Reply | Quote

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

Thank you!

Leesha

Reply | Quote

I’m glad you didn’t have to resort to that ugly workaround!

Reply | Quote

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

Reply | Quote