Error in my code 1

[snyperx3]

My database is working perfectly on my computer which has access 2002, but when its running on a computer that has 2002 runtime i get the following error when its compiled in an mde:

"The expression you entered On Click as the event property setting produced the following error: .
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been and error evaluating the function, event, or macro."

If I run it as the mdb then it gives me a runtime error and crashes.

I've found for previous problems of this nature that I am most likely missing a reference, but after I first saw this problem I, personally, copied the dlls im using to the computer, and registered them. I found the code that is causing this problem by commenting it out and rerunning the program with no errors. The problem is that this section of code works PERFECT on my computer.

Public Sub MovePhysicalInventory()
    '===============================
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblPhysicalInventory")
    Dim rst2 As DAO.Recordset
    Set rst2 = db.OpenRecordset("tblTempPhys")
    rst2.MoveFirst
    Do Until rst2.EOF
        rst.AddNew
        rst![StoreNum] = Right("00000" & rst2![locationid], 5)
        rst![Month] = Left(rst2![phys_invty_dt], 4) & Mid(rst2![phys_invty_dt], 6, 2)
        rst![ItemNum] = rst2![raw_item_num]
        rst![TotalUnits] = rst2![raw_item_invty_qty]
        rst.Update
        rst2.MoveNext
    Loop
    '===============================
    rst.Close: Set rst = Nothing
    rst2.Close: Set rst2 = Nothing
    db.Close: Set db = Nothing
End Sub

This is a function in a module that i am calling from another form.

What missing reference would cause this code not to work?

Thanks for your help

-Pete

 

[PHV]

Does the 2 computers have the same version (probably 3.6) of the Microsoft DAO Object Library ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snyperx3]

yes. both have dao 3.6...and that was also one of the files i copied over just to make sure.

-Pete

 

[PHV]

Does the mdb compiles properly BEFORE creating the mde ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gwinn7]

Have you tried stepping through while executing this code? Could you provide which line the error is being triggered at?

Gary
gwinn7

 

[PHV]

Stepping through an MDE ????

 

[snyperx3]

PHV: Yes the mdb compiles properly. The code works fine on the computer I wrote it on. Both the MDE and MDB work great on my computer.

gwinn7: I cannot step through to the error becuase the only computer it doesnt work on is the one that doesnt have a full version of access2002 installed...its just the run-time. It works properly on my computer, so stepping through wont catch anything, and you cant step through code with access runtime.
I found out it was this section because i went back and programed it to skip the function call, recompiled it, and put it on the other computer again to test and it didnt crash.

-Pete

 

[wtmckown]

Try using the CALL statement to enter your procedure when your button is clicked. i.e. Call MovePhysicalInventory()

 

[snyperx3]

ummm...thats what im doing. I was just showing the procedure that im using. In my form that accesses it i have the line "Call MovePhysicalInventory()". This event is called in the middle of some click event that has several lines of code executed before it.


-Pete

 

[TheAceMan1]

How are ya snyperx3 . . .

. . . and if you change:

[blue]   rst.Close: Set rst = Nothing
   rst2.Close: Set rst2 = Nothing
   db.Close: Set db = Nothing[/blue]

To:

[blue]   Set rst = Nothing
   Set rst2 = Nothing
   Set db = Nothing[/blue]

?

My suspicions lie with [blue]db.Close[/blue] . . .

See Ya! . . . . . .

 

[ZmrAbdulla]

and what about

    Set rst = db.OpenRecordset("tblPhysicalInventory"[b], dbOpenDynaset[/b])
    Dim rst2 As DAO.Recordset
    Set rst2 = db.OpenRecordset("tblTempPhys"[b], dbOpenDynaset[/b])

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[snyperx3]

Thanks for your replies everyone.

TheAceMan1: I use that code in several places throughout my code, and it doesn't crash anywhere else.

ZmrAbdulla: Thanks for noticing that. I usually ALWAYS put dbOpenDynaset on there, just forgot this time. Yet I tried it like that, and it still doesn't work. =\

I received a reply from Microsoft about this, so I'm going to try their solution, and if it works I'll post it on here.

Thanks for your help guys.



-Pete

 

[snyperx3]

I've narrowed the error down to this section:

rst.AddNew
rst![StoreNum] = Right("00000" & rst2![locationid], 5)
rst![Month] = Left(rst2![phys_invty_dt], 4) & Mid(rst2![phys_invty_dt], 6, 2)
rst![ItemNum] = rst2![raw_item_num]
rst![TotalUnits] = rst2![raw_item_invty_qty]
rst.Update

This section DOES work on MY computer. It DOES NOT work on the computer with runtime.


-Pete

 

[wtmckown]

I wasn't trying to insult you with the 'Call' statement suggestion. It's just that I've seen that error thrown in the past if a procedure was called by just using the procedure name without the Call statement. Another thought: I see that you are updating a date field. Is it possible that the PC that is getting the error has Canadian regional date setting? I have seen this when commas are specified in regional settings as date delimiters.

 

[snyperx3]

No it's fine. I didn't take offense to the "Call".

In reference to the date -
What this portion of the program is doing in a nutshell:
It uses transfertext to get values from a CSV file based on the delimeter. They are all imported into text fields. This works fine.
Next, I am changed the values imported into a format which will co-relate with the other values I have in my database. Such that the store numbers are 5-digit long with leading zeros, and that the month is stored as "yyyymm". No value is ever stored as an actual Date, so the computers regional date setting would have no effect on this situation.

This is severly annoying in that all the code executes smoothly and perfectly on my pc, yet doesn't on a pc with access runtime.

I have further narrowed the error in this section of code down to: ...Mid(rst2![phys_invty_dt], 6, 2)



-Pete

 

[abaldwin]

I would look at the data as well. Is it possible that the phys_invty_dt field is null or contains invalid data in the 7th and 8th characters?

Am I to understand from the above posting that the month is this date is YYYYMM format in the CSV file? If so there would only be 6 characters here to look at. Anyway I would look at their actual data instead of your test data and see if you can replicate the problem with the live data on your machine.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[snyperx3]

Andy,

Everything works PERFECTLY on my pc, hence the data cannot be the problem. I have the same file on my pc that they have. If the data was the problem, then it would crash on my computer as well. In the csv file the data is in the format: yyyy-mm-dd. Im converting that to the format yyyymm.

I am working with the actual data.

Thanks for your response.

-Pete

 

[snyperx3]

Andy,

After reviewing your post further I found the error. After uploading the data onto their server, someone working at that site managed to put another file in its place with the SAME file name. The filesize was the same as well, making it seem the same. After reuploading the data and running it right then for them, it worked just fine.

Sorry to everyone for wasting your time in helping me chase a false problem. My apologies for trusting a customer with files.

In the end, it was Andy's advice that brought me to check the data for corruption, so you get the star Andy.

Thanks everyone for your time.



-Pete

 

[wtmckown]

At this point I would do some detective work with regard to that mid function to see if it is the use of the function or the string data that it is being passed that is causing access to choke. Can you replace Mid(rst2![phys_invty_dt], 6, 2) with an example ot the string that you think the function should yield and see if the error throws? Also you could use the mid function with a literal string instead of the recordset reference as a test to see if the access runtime objects to the mid function for some reason.

 

[abaldwin]

Your welcome. When all else is equal....usually the data is the only dynamic part.

Glad it worked out for you.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!