Weird Access problem

irlrobins

Morning all,

Having a strange MS Access problem at the moment. I have an Access Data Project file (.adp) linked to data on a MSSQL 2000 server.

All appears to be fine except that I can't add new records using a form. In addition when I try to delete a record (using DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 and DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70) I get an 2051 error and instead of deleting jsut the current record, the entire table is deleted!

Now, my initial thought was corrupt Access file, but when my colleague runs the exact same adp file on his PC, it works completely correct.

So I'm trying to figure out why the exact same code works fine on one PC and not the other. I can't think of anything so I hope someone can shed some light.

Thanks
R

dzy

I did a quick Google on your problem. I couldn't find anything about error #2051. I did find info about an error #2501. Did you make a typo?

#2501 has to do with mapped network printers and creating database reports. If the project file contains a reference to a printer you haven't mapped you get this error. Your friend might have the printer mapped.

Its just a shot in the dark. I didn't see anything about a table being dropped.

irlrobins

Sorry yes, I meant runtime error 2501. I'll try changing the default printer and see what happens

Yea changing the default printer had not effect. Not that I thought it would. Many of the google results seem to relate to problems with reports (which the printer will have an effect on). But this is to do with the manipulation of data on the database.

And the table isn't dropped, just all rows in it are deleted.

stevire

Did you check your References (ocx) files? Are both machines on the same version of Office?

You could have updated a version of Office and the other machine may have the same version as the one the project was originally created with.

Worth a shot...

irlrobins

Yep, was looking into that. The two laptops have different versions of vbe6.dll and DAO360.dll. I have a third laptop that it runs correctly on so just going to check version numbers on it to see if there is any comparasion.

Funny thing is, the forms were created on my laptop, so you'd expect it to work on mine and not my colleague's.

Update: I removed the reference to DAO as it wasn't required. I then ran it on my laptop (same issues) and on my second colleague's laptop (works fine). Both my laptop and my colleague's laptop have the same version numbers of all the included references.

I then created a blank Access adp file and imported the forms and reports, etc into it. This is a standard method of resolving corrupt forms. Again, won't add new records and does strange delete on my laptop but works fine on another two laptops.

Completely stumped.

irlrobins

Further update:

The form in question is opened from another form using a command button. Part of this command button event is to apply a filter to the form being opened using the Filter and FilterOn properties. If I remove the filter, the form opens and I can add and delete as expected. But with the filter on I experience the problems previously detailed.

Now, that would be the solution, except for the fact that it works fine on four different laptops with the filter applied. So why doesn't it on mine???

stevire

I haven't seen your code but I presume your using:

DoCmd.OpenReport(ReportName, acViewNormal, FilterName)

to open the report?

irlrobins

I'm opening a form, not a report. See code below [PHP]Private Sub btn1_Click()
On Error GoTo Err_btn1_Click

Dim stDocName As String

stDocName = "frmRequirementsByDocNo"
DoCmd.OpenForm stDocName
Forms![frmRequirementsByDocNo].Caption = "All Requirements"
Forms![frmRequirementsByDocNo].Filter = "[Req_NO] is not null and [ProjectNo] =" & GBL_ProjectNo
Forms![frmRequirementsByDocNo].FilterOn = True
Exit_btn1_Click:
Exit Sub

Err_btn1_Click:
MsgBox Err.Description
Resume Exit_btn1_Click

End Sub[/PHP]

dzy

Do you have an error message to go along with the error number?

Since the filter seems to be causing the problem could you perhaps try another (simpler) filtering condition as a test? Are you sure you have named the fields correctly in the filter? There seems to be inconsistency in your field names Req_NO and ProjectNo.

I'd play around with the filter a bit to see if you can get a filter that does work.

stevire

Shouldn't have to change much of the code if it is working on other laptops. Might be a simple one, but is Name Autocomplete checked on in Tools -> Options -> General?

Try switching this off, although that function usually causes errors opening the form not while its open. No harm trying anyway...

irlrobins

dzy wrote: »

Do you have an error message to go along with the error number?

"The DoMenuItem action was cancelled"

dzy wrote: »

Are you sure you have named the fields correctly in the filter?

Yep, they're fine

stevire wrote:

Shouldn't have to change much of the code if it is working on other laptops.

Shouldn't have to change any code really, since it works on every other laptop I've tried it on. The laptops are pretty much identical (image install). So the code is correct , just behaves peculiarly on my laptop.

stevire wrote:

Might be a simple one, but is Name Autocomplete checked on in Tools -> Options -> General?

I can't see this option. (Access 2003)

irlrobins

The plot thickens:

I made some changes to the code. Rather than open the form and then apply the filter programatically:

[PHP]stDocName = "frmRequirementsByDocNo"
DoCmd.OpenForm stDocName
Forms![frmRequirementsByDocNo].Caption = "All Requirements"
Forms![frmRequirementsByDocNo].Filter = "[Req_NO] is not null and [ProjectNo] =" & GBL_ProjectNo
Forms![frmRequirementsByDocNo].FilterOn = True [/PHP]

I used this code instead:

[PHP]DoCmd.OpenForm "frmRequirementsByDocNo", acNormal, , "Req_No Is Not Null And ProjectNo = " & GBL_ProjectNo[/PHP]

And now the add and delete works fine, on all laptops.


But...

If I try to apply my second filter using the OpenForm method:

[PHP]DoCmd.OpenForm "frmRequirementsBySFPStream", acNormal, , "SFP_Stream ='" & Me.SFP_Stream & "' And Req_No Is Not Null And ProjectNo = " & GBL_ProjectNo[/PHP]

I get the same add and delete problems across all the laptops!

The form opens and the correct data is filtered. I can edit existing records, but can't add (clicking the "add record" button gives me a blank record, but when I click into any field and try to type, nothing is entered) and deleting removes all the records from the table.

If I adjust the code to:
[PHP]DoCmd.OpenForm "frmRequirementsBySFPStream", acNormal, , "Req_No Is Not Null And ProjectNo = " & GBL_ProjectNo[/PHP]

the form functions correctly.

So why does the inclusion of SFP_Stream ='" & Me.SFP_Stream fuck it all up? ARG!!!

stevenmu

What are the potential values of Me.SFP_Stream? Could it have some kind of invalid character ?

irlrobins

The values of SFP_Stream is populated by the date within a table. The data is of the form 1.A, 1.B,....., 2.A, etc. I've even hard coded a value in and the same behaviour occurs.

tiptap

irlrobins wrote: »

The values of SFP_Stream is populated by the date within a table. The data is of the form 1.A, 1.B,....., 2.A, etc. I've even hard coded a value in and the same behaviour occurs.

If it's a date, is it perhaps that your regional settings are different to the computers that are working

irlrobins

irlrobins wrote: »

The data is of the form 1.A, 1.B,....., 2.A, etc.

tiptap wrote: »

If it's a date...

Mmm, obviously it's not

drag0n79

And if you build the filter string first?
strFilter = "SFP_Stream='" & Me.SFP_Stream & "' And Req_No Is Not Null And ProjectNo= " & GBL_ProjectNo

Then
DoCmd.OpenForm "frmRequirementsBySFPStream", acNormal, , strFilter

Also make sure your types match (i.e. type of SFP_Stream in frontend & backend).

irlrobins

Haven't tried this yet, but I will tomorrow. This is still puzzling the hell out of me...

irlrobins

Well tried the string thing but acts in the same manner. The records are correctly filtered, but when I click on the "new record" button, I can't enter any text into any of the fields.

Anyone else??

drag0n79

What is Me.SFP_Stream ? Are you sure its type matches type of field in backend?

Regarding your previous problem (working on some PCs, not others): check Office 2003 service packs & hotfixes, patches etc.

irlrobins

Me.SFP_Stream is a combobox that is populated with data from a table called SFP_Stream. It's type and length matches the table that the form would populate.

dzy

irlrobins wrote: »

Anyone else??

Have you tried dancing around your computer while chanting the name of the lead Access developer five times backwards?

Sorry dude. I'd try narrowing it down. Just filter on the SFP_Stream field and nothing else. Does that work? Try using some simple data string as your filter value. Maybe try just the empty string. Does that work? Make sure to have whitespace around the '=' equality condition in your filter expression (I know - retarded - but who knows what lurks under the covers). Just play around with the filter expression and see what does and doesn't work. You might see a pattern there that'll lead you to the problem.