Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Database replacing data on own (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    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.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    To which thread are you replying now?

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    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).

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    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.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    I don't see anything in this code that would change a name field either...

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database replacing data on own (2000)

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

    Is the Account Code field bound or unbound?

    Pat

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    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...

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database replacing data on own (2000)

    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database replacing data on own (2000)

    Hey! I asked you in <post#=245380>post 245380</post#> whether the list box was bound and in <post#=245384>post 245384</post#> you replied that it wasn't! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •