Data is changing that shouldnt

[truthlover]

I am having a very perplexing problem that I'm hoping someone can help me solve. It's a little difficult to explain, but I'll do my best. I'm also providing a link to the file so you can take a closer look.

What I have is a form that is used to enter details of time spent on a job, the rate of the task and who performed the task. The form allows the user to choose the project they want to provide details for with a subform where the details are actually entered/edited. There is a button on the form that allows the user to print a summary of the details.

Up until now, everything seemed to be working fine. But the other day, the person maintaining this process deleted a record. The record he deleted was deleted with no problem, but somehow, when the record was deleted for Project 08.0136.01, a record for Project 08.0126.01 somehow had the project number changed to Project 08.0136.01.

It doesnt seem to happen when a record from 08.0126.01 is deleted because there are no records in the table after it (though I'm sure if the table was re-sorted to put those records first, but same thing would happen).

I thought it might have to do with the subform referencing a query instead of the table, so I changed the source to the table, and the same thing happened.

For the life of me, I cannot figure out how that could happen or how to fix it. If anyone can help, I'd be most grateful. Here's the link to the file http://www.stellarwebcreations.com/C...ataProblem.zip.

The form in question is frm_EditConstructionLog. If you choose project 08.0136.01 from the dropdown and delete one of the records, you'll see what I'm tryin to explain. The simplest way to see the problem is to run the "Print Construction Log" You'll see the totals do not add up to what they should if just the one record was deleted, and the record that had the number changed will appear in Project 08.0136.01.

BTW I'm using Access 2002 to create the database but it is saved in 2000 format.

Thanks so much!

[MGrowneyARSI]

Well there is no code behind to combo box so you can't select 136 and how are you deleting records you have not option or button. It looks like your using the link child and master fields property. I've never liked using them and always recommend you move that restriction over to the queries. I can see that this is a chopped down version of your DB since frm_menu is not there. so maybe so other things are missing as well. in order to give you any help at all I would need to no how you are going about deleting the record. please post code.

[truthlover]

Well there is no code behind to combo box so you can't select 136

Not sure what you mean. I just opened the file (yes, it is cut down) and there is a combo box right above the subform (label = Choose Project Number) If you go into the properties of that box, you will see where it is pulling the data from.

in order to give you any help at all I would need to no how you are going about deleting the record. please post code.

The records are being deleted directly from the sub form (just clicking on the record selector and deleting the record)

Thanks!

[MGrowneyARSI]

Wups my fault I was having issue because I was in read only mode I had to use IE because FF would not let me download your file however I'm not seeing the same issue as you, however when I deleted multiple records in a row I experienced what is called stepping on a record, this is because your not forcing a save after delete. I think the issue is just because your basically allowing the user to edit the table directly however they want the form is not locked down. I sorry I can't really help personally I wold have a different form for the user to edit delete and add records instead of using the data sheet.

[truthlover]

I'm not seeing the same issue as you, however when I deleted multiple records in a row I experienced what is called stepping on a record, this is because your not forcing a save after delete.

You have to delete a record from Project 08.136.01 to see it happen. Can you explain "stepping on a record"?

I think the issue is just because your basically allowing the user to edit the table directly however they want the form is not locked down. I sorry I can't really help personally I wold have a different form for the user to edit delete and add records instead of using the data sheet.

Yes, i'm sure the way the form is constructed is the root of the problem, but I havent been able to come up with a way to prevent the errors in its current state or create an alternate solution that would allow the user to choose the project they want to edit AND have a view of all the records for that project.

A Continuous view form seemed like the best solution, but I couldnt figure out how to get the dropdown to filter the way the current form is working.

I'm sure there's a way, I'm just not sure what that is. I'm good with Access basics, but when it comes to issues like these, I need help. I also dont know VBA yet, so I dont have that knowledge to draw on.

So if you can suggest a better method of accomplishing my goals, I'd love to hear about it.

Thanks!

[MGrowneyARSI]

Well I do have something similar in one of my DBs but instead of allowing them to edit the record on the data sheet I created another sub form that they add edit and delete the records from, what i do is put a click event on all of the fields in the datasheet and use code like this

    Dim rs As Object
    Forms!Order_Quote!Edit_Detail.Requery
    Set rs = Forms!Order_Quote!Edit_Detail.Form.Recordset.Clone
    rs.FindFirst "[OI_ID] = " & Me.OI_ID
    If Not rs.EOF Then Forms!Order_Quote!Edit_Detail.Form.Bookmark = rs.Bookmark
    Forms!Order_Quote!Edit_Detail.Form.Refresh

it's kind of a weird way to do things but it works.

to explain the code a little better
first you have the (dim rs as object) this is needed so access knows what you mean by rs

then we re query the new sub form this is optional so we refer to ( Forms!The name of the form your on!the name of your new subform.form.requery ) this will force the query behind the form to refresh itself

then you'll notice that you refer to the new sub form again the same as before but this time it's (Set rs bla bla bla.form.recordset.clone

then you find the record you want ( were you see OI_ID just insert the field name of your PK or whatever you use to identify the records.

the rest should be self explanatory let me know if it helps

[truthlover]

Well I do have something similar in one of my DBs but instead of allowing them to edit the record on the data sheet I created another sub form that they add edit and delete the records from, what i do is put a click event on all of the fields in the datasheet and use code like this

 Dim rs As Object
Forms!Order_Quote!Edit_Detail.Requery
Set rs = Forms!Order_Quote!Edit_Detail.Form.Recordset.Clone
rs.FindFirst "[OI_ID] = " & Me.OI_ID
If Not rs.EOF Then Forms!Order_Quote!Edit_Detail.Form.Bookmark = rs.Bookmark
Forms!Order_Quote!Edit_Detail.Form.Refresh

it's kind of a weird way to do things but it works.

to explain the code a little better
first you have the (dim rs as object) this is needed so access knows what you mean by rs

then we re query the new sub form this is optional so we refer to ( Forms!The name of the form your on!the name of your new subform.form.requery ) this will force the query behind the form to refresh itself

then you'll notice that you refer to the new sub form again the same as before but this time it's (Set rs bla bla bla.form.recordset.clone

then you find the record you want ( were you see OI_ID just insert the field name of your PK or whatever you use to identify the records.

the rest should be self explanatory let me know if it helps

I got the gist of what you were saying, but the rest may as well be in another language. That's the problem with not knowing VBA -- even the simplest instructions are confusing.

At any rate, I've found a suitable solution for the moment.

However if you (or anyone else out there) can tell me how to get a dropdown combo box to pull up specific records, I'd be so grateful.

Thanks for your help!

[MGrowneyARSI]

when you go to create a combo or list box it gives you 3 options chose the last one something like "find a record record on your form based on what I selected from my combo box" and it will take you through a wizard.

The code i just gave was pulled from behind a combo box and modified to do what I told it to. But if you go through the wizard like i said access will create the code for you.

you really can't do anything in Access without VB6 so I highly recommend you pick up a book and start to learn I picked up the just of it in about 2 weeks and you just kind of keep learning new tricks from there.

[truthlover]

when you go to create a combo or list box it gives you 3 options chose the last one something like "find a record record on your form based on what I selected from my combo box" and it will take you through a wizard.

The code i just gave was pulled from behind a combo box and modified to do what I told it to. But if you go through the wizard like i said access will create the code for you.

you really can't do anything in Access without VB6 so I highly recommend you pick up a book and start to learn I picked up the just of it in about 2 weeks and you just kind of keep learning new tricks from there.

I'll give that a try, thanks.

As for learning VBA, i've just recently started reading a pretty good book (I've already solved a few problems with the little I've learned) Problem is I've got so little time to read it (working 3 jobs and the gaps between my time reading make it harder to retain) but I am learning. Now if only the issues with the db I'm working with would be so kind as to not show up until I've learned more ;)

But until then, I'm very grateful for your help and the help of everyone else in this forum. I couldnt do it without you!

Thanks!

[truthlover]

I'll give that a try, thanks.

Ok, I just tried the combo box and it didnt work the way I need it to work.

I'm using a Continuous view form, and I need a combo box to filter the results.

Got any ideas how to do this with a dropdown?

Thanks!

[NeoPa]

You could try looking at Example Filtering on a Form. Let us know how you get on with it.

[truthlover]

Ok, I tried doing this. I shortened the code since all I need is the combo box. I also set the source code for the combo box to a query list of the available project numbers (rather than a value list the way the zip file has it set as)

'CheckFilter produces the new Filter depending on the values currently in cboFindAccountType.
Private Sub CheckFilter()
Dim strFilter As String, strOldFilter As String
strOldFilter = Me.Filter
'cboChooseProjectNumber - Numeric
If Me!cboChooseProjectNumber > "" Then _
strFilter = strFilter & _
" AND ([ProjectID]=" & _
Me!cboChooseProjectNumber & ")"
'Debug.Print ".Filter = '" & strOldFilter & "' - ";
'Debug.Print "strFilter = '" & strFilter & " '"
'Tidy up results and apply IF NECESSARY
If strFilter > "" Then strFilter = Mid(strFilter, 6)
If strFilter <> strOldFilter Then
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")
End If
End Sub
 

But when I run it, I get this:

runtime error #3075

syntax error in number in query expression
'([ProjectID=08.0136.01)'

When I do the debug, it highlights line 15 (but I dont know how to fix it)


Any suggestions?

Thanks!!

You could try looking at Example Filtering on a Form. Let us know how you get on with it.

[Stewart Ross]

Hi truthlover. Your filter expression is not correctly-formed. You are referring to the [project ID] field as a number, not a string, by missing single quotes out. Lines 8 and 9 should be

" AND ([ProjectID] = '" & Me.cboChooseProjectNumber & "')"

-Stewart

[NeoPa]

Nice catch Stewart :)

@TruthLover - That was a fair attempt at implementing the logic from the article.
I'm curious though, why you dropped the code indentation. Properly indented code can be so much easier to read and understand. Was this an accidental by-product of your preparation of the post perhaps?

[truthlover]

Hi truthlover. Your filter expression is not correctly-formed. You are referring to the [project ID] field as a number, not a string, by missing single quotes out. Lines 8 and 9 should be

" AND ([ProjectID] = '" & Me.cboChooseProjectNumber & "')"

-Stewart

Yes, the ProjectID is a string. I didnt change the notes (that referred it to a number) but I had a feeling the fact that it was a string was the problem (I just didnt know how to make the correction).

I'll give it a try and get back to you.

Thanks!

[truthlover]

Nice catch Stewart :)

@TruthLover - That was a fair attempt at implementing the logic from the article.
I'm curious though, why you dropped the code indentation. Properly indented code can be so much easier to read and understand. Was this an accidental by-product of your preparation of the post perhaps?

Thanks Neo

I didnt drop the indents, though. I just copy/pasted and that was the results. I couldnt remember how to code the code for vb instead of text. Guess I should have reinserted the indents to make it easier to read.

Thanks

[truthlover]

Yes, the ProjectID is a string. I didnt change the notes (that referred it to a number) but I had a feeling the fact that it was a string was the problem (I just didnt know how to make the correction).

I'll give it a try and get back to you.

Thanks!

It worked great, thanks!!!! You made my week :)

[NeoPa]

Normally the VB code is done as "[ CODE=VB ]" (No spaces or quotes). It's not working at the moment as there were too many issues with the libraries used for this. We're currently looking for replacements.

No worries about the indenting. I was only really interested if you'd decided explicitly to remove them.

PS. Glad you got it working ;)