Inconsistent If Then results on different PC’s

Question

Hello

We have an access database(2007 - 2016 format)...we have 10 users using this database ,database  resides on the individual pc's , 1 user  is getting a different result than everyone else using Private Sub Form_Load() when button is selected to open a form the code determines what fields are active on the form based on the Job Type field. Even when the user opens the database on the server they are still receiving the incorrect response. The job type is a 1, 2 or 3.   This user receives the result as if the job type is always a 2.  All machines are running the same operating system.

Any suggestions what I can look at to see what is causing this?

This is a snippet of the code

If (Me.Job_Type = 1) Then
Resinous_Order.Locked = False
Res_Resins.Locked = False
Res_Aggreg.Locked = False
Res_Strip.Locked = False

Thank you

Mary

Answer 1

We'd need to see the either procedure.

 

I'd probably start with a decompile, C&R, Compile, C&R.
Are all your user running the same version and bitness of Access?
What version and bitness was used to create/develop the database?
Are you distributing an accdb or accde?  If the former, does it compile on the users computer without errors?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

If the form was saved on close that might have set the .Locked property. You should have an "Else" to clear the locks or do it this way and do away with the If statement:

Resinous_Order.Locked = Not (Me.Job_Type = 1)

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 3

here is the entire sub

'Resinous
If (Me.Job_Type = 1) Then
Resinous_Order.Locked = False
Res_Resins.Locked = False
Res_Aggreg.Locked = False
Res_Strip.Locked = False

Resinous_Order.TabStop = True
Res_Resins.TabStop = True
Res_Aggreg.TabStop = True
Res_Strip.TabStop = True

Resinous_Order.Visible = True
Res_Resins.Visible = True
Res_Aggreg.Visible = True
Res_Strip.Visible = True

'Hide Terrazo
Terrazzo_Order.TabStop = False
Terr_Resins.TabStop = False
Terr_Sealer.TabStop = False
Terr_Strip.TabStop = False
Terr_Aggreg.TabStop = False
Terr_ATF.TabStop = False
Terr_Base.TabStop = False
Terr_Stairs.TabStop = False
Terr_Grout.TabStop = False

Terrazzo_Order.Visible = False
Terr_Resins.Visible = False
Terr_Sealer.Visible = False
Terr_Strip.Visible = False
Terr_Aggreg.Visible = False
Terr_ATF.Visible = False
Terr_Base.Visible = False
Terr_Stairs.Visible = False
Terr_Grout.Visible = False

'Hide Decorative Concrete
Decorative_Concrete_Order.TabStop = False
Dec_densifier.TabStop = False
Dec_Sealer.TabStop = False
Dec_Joint.TabStop = False
Dec_Diamonds.TabStop = False
Dec_Overlay.TabStop = False
Dec_Stain.TabStop = False

Decorative_Concrete_Order.Visible = False
Dec_densifier.Visible = False
Dec_Sealer.Visible = False
Dec_Joint.Visible = False
Dec_Diamonds.Visible = False
Dec_Overlay.Visible = False
Dec_Stain.Visible = False

End If

'Terrazzo
If (Me.Job_Type = 2) Then
Terrazzo_Order.Locked = False
Terr_Resins.Locked = False
Terr_Sealer.Locked = False
Terr_Strip.Locked = False
Terr_Aggreg.Locked = False
Terr_ATF.Locked = False
Terr_Base.Locked = False
Terr_Stairs.Locked = False
Terr_Grout.Locked = False
 
Terrazzo_Order.TabStop = True
Terr_Resins.TabStop = True
Terr_Sealer.TabStop = True
Terr_Strip.TabStop = True
Terr_Aggreg.TabStop = True
Terr_ATF.TabStop = True
Terr_Base.TabStop = True
Terr_Stairs.TabStop = True
Terr_Grout.TabStop = True

Terrazzo_Order.Visible = True
Terr_Resins.Visible = True
Terr_Sealer.Visible = True
Terr_Strip.Visible = True
Terr_Aggreg.Visible = True
Terr_ATF.Visible = True
Terr_Base.Visible = True
Terr_Stairs.Visible = True
Terr_Grout.Visible = True

'Hide Resinous
Resinous_Order.TabStop = False
Res_Resins.TabStop = False
Res_Aggreg.TabStop = False
Res_Strip.TabStop = False

Resinous_Order.Visible = False
Res_Resins.Visible = False
Res_Aggreg.Visible = False
Res_Strip.Visible = False

'Hide Decorative Concrete
Decorative_Concrete_Order.TabStop = False
Dec_densifier.TabStop = False
Dec_Sealer.TabStop = False
Dec_Joint.TabStop = False
Dec_Diamonds.TabStop = False
Dec_Overlay.TabStop = False
Dec_Stain.TabStop = False

Decorative_Concrete_Order.Visible = False
Dec_densifier.Visible = False
Dec_Sealer.Visible = False
Dec_Joint.Visible = False
Dec_Diamonds.Visible = False
Dec_Overlay.Visible = False
Dec_Stain.Visible = False

End If
   
'Decorative Concrete
If (Me.Job_Type = 3) Then
Decorative_Concrete_Order.Locked = False
Dec_densifier.Locked = False
Dec_Sealer.Locked = False
Dec_Joint.Locked = False
Dec_Diamonds.Locked = False
Dec_Overlay.Locked = False
Dec_Stain.Locked = False

Decorative_Concrete_Order.TabStop = True
Dec_densifier.TabStop = True
Dec_Sealer.TabStop = True
Dec_Joint.TabStop = True
Dec_Diamonds.TabStop = True
Dec_Overlay.TabStop = True
Dec_Stain.TabStop = True

Decorative_Concrete_Order.Visible = True
Dec_densifier.Visible = True
Dec_Sealer.Visible = True
Dec_Joint.Visible = True
Dec_Diamonds.Visible = True
Dec_Overlay.Visible = True
Dec_Stain.Visible = True

'Hide Terrazzo
Terrazzo_Order.TabStop = False
Terr_Resins.TabStop = False
Terr_Sealer.TabStop = False
Terr_Strip.TabStop = False
Terr_Aggreg.TabStop = False
Terr_ATF.TabStop = False
Terr_Base.TabStop = False
Terr_Stairs.TabStop = False
Terr_Grout.TabStop = False

Terrazzo_Order.Visible = False
Terr_Resins.Visible = False
Terr_Sealer.Visible = False
Terr_Strip.Visible = False
Terr_Aggreg.Visible = False
Terr_ATF.Visible = False
Terr_Base.Visible = False
Terr_Stairs.Visible = False
Terr_Grout.Visible = False

'Hide Resinous
Resinous_Order.TabStop = False
Res_Resins.TabStop = False
Res_Aggreg.TabStop = False
Res_Strip.TabStop = False

Resinous_Order.Visible = False
Res_Resins.Visible = False
Res_Aggreg.Visible = False
Res_Strip.Visible = False

End If
End Sub

Answer 4

All users are running the same version of Access

All 64 bit

Yes it does compile without issue

here is the entire  sub

'Resinous
If (Me.Job_Type = 1) Then
Resinous_Order.Locked = False
Res_Resins.Locked = False
Res_Aggreg.Locked = False
Res_Strip.Locked = False

Resinous_Order.TabStop = True
Res_Resins.TabStop = True
Res_Aggreg.TabStop = True
Res_Strip.TabStop = True

Resinous_Order.Visible = True
Res_Resins.Visible = True
Res_Aggreg.Visible = True
Res_Strip.Visible = True

'Hide Terrazo
Terrazzo_Order.TabStop = False
Terr_Resins.TabStop = False
Terr_Sealer.TabStop = False
Terr_Strip.TabStop = False
Terr_Aggreg.TabStop = False
Terr_ATF.TabStop = False
Terr_Base.TabStop = False
Terr_Stairs.TabStop = False
Terr_Grout.TabStop = False

Terrazzo_Order.Visible = False
Terr_Resins.Visible = False
Terr_Sealer.Visible = False
Terr_Strip.Visible = False
Terr_Aggreg.Visible = False
Terr_ATF.Visible = False
Terr_Base.Visible = False
Terr_Stairs.Visible = False
Terr_Grout.Visible = False

'Hide Decorative Concrete
Decorative_Concrete_Order.TabStop = False
Dec_densifier.TabStop = False
Dec_Sealer.TabStop = False
Dec_Joint.TabStop = False
Dec_Diamonds.TabStop = False
Dec_Overlay.TabStop = False
Dec_Stain.TabStop = False

Decorative_Concrete_Order.Visible = False
Dec_densifier.Visible = False
Dec_Sealer.Visible = False
Dec_Joint.Visible = False
Dec_Diamonds.Visible = False
Dec_Overlay.Visible = False
Dec_Stain.Visible = False

End If

'Terrazzo
If (Me.Job_Type = 2) Then
Terrazzo_Order.Locked = False
Terr_Resins.Locked = False
Terr_Sealer.Locked = False
Terr_Strip.Locked = False
Terr_Aggreg.Locked = False
Terr_ATF.Locked = False
Terr_Base.Locked = False
Terr_Stairs.Locked = False
Terr_Grout.Locked = False
 
Terrazzo_Order.TabStop = True
Terr_Resins.TabStop = True
Terr_Sealer.TabStop = True
Terr_Strip.TabStop = True
Terr_Aggreg.TabStop = True
Terr_ATF.TabStop = True
Terr_Base.TabStop = True
Terr_Stairs.TabStop = True
Terr_Grout.TabStop = True

Terrazzo_Order.Visible = True
Terr_Resins.Visible = True
Terr_Sealer.Visible = True
Terr_Strip.Visible = True
Terr_Aggreg.Visible = True
Terr_ATF.Visible = True
Terr_Base.Visible = True
Terr_Stairs.Visible = True
Terr_Grout.Visible = True

'Hide Resinous
Resinous_Order.TabStop = False
Res_Resins.TabStop = False
Res_Aggreg.TabStop = False
Res_Strip.TabStop = False

Resinous_Order.Visible = False
Res_Resins.Visible = False
Res_Aggreg.Visible = False
Res_Strip.Visible = False

'Hide Decorative Concrete
Decorative_Concrete_Order.TabStop = False
Dec_densifier.TabStop = False
Dec_Sealer.TabStop = False
Dec_Joint.TabStop = False
Dec_Diamonds.TabStop = False
Dec_Overlay.TabStop = False
Dec_Stain.TabStop = False

Decorative_Concrete_Order.Visible = False
Dec_densifier.Visible = False
Dec_Sealer.Visible = False
Dec_Joint.Visible = False
Dec_Diamonds.Visible = False
Dec_Overlay.Visible = False
Dec_Stain.Visible = False

End If
   
'Decorative Concrete
If (Me.Job_Type = 3) Then
Decorative_Concrete_Order.Locked = False
Dec_densifier.Locked = False
Dec_Sealer.Locked = False
Dec_Joint.Locked = False
Dec_Diamonds.Locked = False
Dec_Overlay.Locked = False
Dec_Stain.Locked = False

Decorative_Concrete_Order.TabStop = True
Dec_densifier.TabStop = True
Dec_Sealer.TabStop = True
Dec_Joint.TabStop = True
Dec_Diamonds.TabStop = True
Dec_Overlay.TabStop = True
Dec_Stain.TabStop = True

Decorative_Concrete_Order.Visible = True
Dec_densifier.Visible = True
Dec_Sealer.Visible = True
Dec_Joint.Visible = True
Dec_Diamonds.Visible = True
Dec_Overlay.Visible = True
Dec_Stain.Visible = True

'Hide Terrazzo
Terrazzo_Order.TabStop = False
Terr_Resins.TabStop = False
Terr_Sealer.TabStop = False
Terr_Strip.TabStop = False
Terr_Aggreg.TabStop = False
Terr_ATF.TabStop = False
Terr_Base.TabStop = False
Terr_Stairs.TabStop = False
Terr_Grout.TabStop = False

Terrazzo_Order.Visible = False
Terr_Resins.Visible = False
Terr_Sealer.Visible = False
Terr_Strip.Visible = False
Terr_Aggreg.Visible = False
Terr_ATF.Visible = False
Terr_Base.Visible = False
Terr_Stairs.Visible = False
Terr_Grout.Visible = False

'Hide Resinous
Resinous_Order.TabStop = False
Res_Resins.TabStop = False
Res_Aggreg.TabStop = False
Res_Strip.TabStop = False

Resinous_Order.Visible = False
Res_Resins.Visible = False
Res_Aggreg.Visible = False
Res_Strip.Visible = False

End If
End Sub

Answer 5

Replaced the multi End IF with Else.....still not displaying the correct data

Answer 6

All users are running the same version of Access

All 64 bit

here is the entire  sub

Hi PDC_MEM,

Apart from your question you could make this sub a little more efficient with respect to the programming.

When a control is not visible, then the Locked property and the TabStop property are not relevant. So in your form you can initialize (set default) all controls to:

    .Visible = False
    .Locked = False
    .TabStop = True

Then you can make 3 subs per Type:

Sub Set_Resinous(cur_state As Boolean)
    Resinous_Order.Visible = cur_state
    Res_Resins.Visible = cur_state
    Res_Aggreg.Visible = cur_state
    Res_Strip.Visible = cur_state
End Sub

Sub Set_Terrazzo(cur_state As Boolean)
    Terrazzo_Order.Visible = cur_state
    Terr_Resins.Visible = cur_state
    Terr_Sealer.Visible = cur_state
    Terr_Strip.Visible = cur_state
    Terr_Aggreg.Visible = cur_state
    Terr_ATF.Visible = cur_state
    Terr_Base.Visible = cur_state
    Terr_Stairs.Visible = cur_state
    Terr_Grout.Visible = cur_state
End Sub

Sub Set_Decorartive_Concrete(cur_state As Boolean)
    Dec_densifier.Visible = cur_state
    Dec_Sealer.Visible = cur_state
    Dec_Joint.Visible = cur_state
    Dec_Diamonds.Visible = cur_state
    Dec_Overlay.Visible = cur_state
    Dec_Stain.Visible = cur_state
End Sub

Then you main Sub reduces to

Select Case Me.Job_Type
Case 1
  Set_Resinous True
  Set_Terrazzo False
  Set_Decorative False
Case 2
  Set_Resinous False
  Set_Terrazzo True
  Set_Decorative False
Case 3
  Set_Resinous False
  Set_Terrazzo False
  Set_Decorative True
End Select

If this Sub is only run in the Load event, you can even omit the backsetting to False.

It becomes now very easy if you want to add some other controls in whatever Job_Type.

Perhaps it also helps in understanding where the problem occurs.

Imb.

Answer 7

Hold on a sec Mary...let's go back and do some ACCESS environment checking first. You say:

"...we have 10 users using this database ,database  resides on the individual pc's..."

Are you telling us that you have a split database and the Backend file is on a shared-drive on a server and each user has their own copy of the Frontend file? Or are you saying each user has their own database which resides on their computer? There is a big difference here. If you are NOT running ACCESS in a multi-user environment (one Backend data tables file shared by all users on a shared-drive) then each individual user has their own data tables. That means each user has the ability to change anything in thier own data tables. Unless that one user has the ability and knowledge to change the code on his own database file, then it's not the code that is the problem.

Another question. You said..."Even when the user opens the database on the server they are still receiving the incorrect response"

What did you mean by this? What database on the server? Are users able to use a different database that is housed on the server? Or are you referring to the Backend data table file I mentioned earlier? Again, this makes a huge difference in how we can help you because it brings into play many other issues besides the code you posted. Is the code you posted from the user who is having the issue, or is the code you posted from someone elses database file?

Answer 8

Sorry for not being clear....backend file is on the server, so all users are accessing the same tables.

What I meant by "Even when the user opens the database on the server they are still receiving the incorrect response"...I have a copy of the "frontend" database on the server, so when it is installed onto a new PC I pull it off the server...so all users have the same "frontend"....when I opened the "frontend" database that is on the server directly from the PC....it still returned the worng data

Answer 9

So they are given an initial copy and they keep using the same copy over and over, or do they get a new copy every time they launch the db?

If it is the former, reuse the same one over and over, have you tried giving them a new fresh copy from the server?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 10

What I meant by "Even when the user opens the database on the server they are still receiving the incorrect response"...I have a copy of the "frontend" database on the server, so when it is installed onto a new PC I pull it off the server...so all users have the same "frontend"....when I opened the "frontend" database that is on the server directly from the PC....it still returned the worng data

Hi PDC_MEM,

Did the server get a new (faulty) update of the frontend, of which only that one person got a copy of?

Imb.

Answer 11

They reuse the same copy...I did download a "new" copy from the server....I have not tried copying one from another user...I will do that

Answer 12

Took a database from another user, that does work properly ...still same issue

Answer 13

I know someone mentioned it earlier, but did you check the form's Filter property, and event the Record source to ensure no filter is applied accidentally by default?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 14

Data tables are on a backend...and there are no filters

Answer 15

When this one user opens their form without doing anything else, does the form display the same record as everyone else?

What does it mean "...receives the result as if the job type is always a 2"?

Do you mean the form displays a different record than everyone else?

Answer 16

Yes....regardless of the "job type"....the sub is always returning data as if the "job type" is a "2"....I just found out that the PC in question is running 32bit    everyone else is running 64 bit.....I am presuming that this may be causing an issue?

Answer 17

I just found out that the PC in question is running 32 bit....all others are running 64 bit....could that causing an issue?

Answer 18

Should be more specific.....all PC's are running 32 bit office......pc in question is running 32 bit OS....."good" pc's are running 64 bit OS

Answer 19

The OS bit version should not be an issue as long as everyone has the 32 version of Office and ACCESS.

If you open the database on the PC in question and then look at the forms Record Source, are all the records there? Are all the job types 1,2 and 3 there? Or is the Record Source only returning Job type 2? If so, then all the PC's should be doing the same. If the PC in question only returns records with job type 2, and the other ones return records with all job types, then someone has been changing the Record Source of the form on that PC only.

One other thing. While you have the database open on the PC in question, make sure it is connected to the proper Backend file by opening the Linked Table Manager and looking at the file it's connected to.

If everyone uses the same ACCESS version and the same Frontend file and the same Backend file, then they are all running the same VBA code too.

Answer 20

It is connected to the correct database.  There is a field on the form that displays the Job Type Name correctly, yet the detail that is dependent on the job type is not being selected properly.  It is selected based on the sub that runs when the form is opened...below is an example of the correct display..you can see that the Job Type drives the detail...PC in question...regardless of what is displayed as the Job Type...."Terrazzo Order" detail always displays

Answer 21

Are you saying Job_Type is a text field and not a number 1,2, or 3? If it is, then your VBA code is using the wrong criteria. But all the users will have the same issue. The criteria in your VBA code is using Job_Type 1,2 or 3. I also see 2 Job_Type fields. Each with different data in them. I can't read them, but can tell they are different. Are these Job_Type fields on 2 different forms? Or is one form a sub-form of the other, I can't tell.

Also, is the underlined Terrazzo Order field a calculated field based on some criteria or is it a label? If it's calculated, then you should check the Record Source and the Default Values for that field to determine they are what you want.

It bothers me that only one user is having a problem though. All users should be having the same problem. It's very difficult to diagnose these kinds of issues without actually being able to open the file myself and look at your form and Record Sources, queries etc.

And you are sure that the form is not automatically filtering. You might open your development version (you said it was 2007) and check the forms Filter property. Make sure it is blank. But again if you distributed the same copy to everyone, then everyone should have the same issues. After 17 years of ACCESS experience, I'm stumpted.

Answer 22

PDC_MEM,

Might or might not work.

Perhaps the OnLoad event is or might be the reason for the value of 2 to appear first. Access forms sometimes lock the original settings of a default value that you might have set to when you save the form even if you have deleted or remove it during programming or design stage.

Second reason is that the values from the server end might be slow to update to the control "Me.Job_Type", which is in a sense, the delay happens and the form will load the phantom value during design stage. Hence, your code runs on the "OnLoad" event.

Try to move the code into the OnOpen event of the form.

Answer 23

OK....finally have had time to work directly on PC in question.....it appears that the sub within OnLoad....and/or OnOpen just is not running.....I have changed it to hide other fields.....but no effect.....any idea why the sub would not run?  Is something possible disabled within this load of Access?

 

Answer 24

GOT IT!

Macros had been disable under Trust Center.......that was a hard lesson learned!

Thank you for all your suggestions!