Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 02:15 PM   #1
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 34
Thanks: 39
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Form opens, but the subform doesn't display correctly

I have inherited a ticket tracking database, where when a ticket is created, you can specify if a piece of equipment has been tagged for a RMA and it generates a RMA form for that piece of equipment.

This issue is a RMA form is generated for every piece of equipment, whether it was identified as a RMA or not.

I've narrowed it down to the following bit of code:

[RMA Transfer] is a checkbox tied to a linked table from SQL with its Data Type as bit

Code:
Dim varX As Variant
varX = DLookup("[RMA Transfer]", "dbo_nFacilityTicketEquipment", "[TicketID]= " & Me.TicketID)

If varX = True Then
<NoMacro runs> DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"
else
<YesMacro runs> DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"
End if
This runs and the Facilities form opens to the correct facility and shows all tickets in a subform, however using the "varX=true" is being ignored as it returns as a -1 or 0 so even though the "OpenForm" line works correctly and displays all tickets, a RMA report is generated even though equipment wasn't marked for a RMA


If I change the code from above to

Code:
If DLookup("[RMA Transfer]", "dbo_nFacilityEquipment", "[TicketID]= " & Me.TicketID) = "-1" Then
<YesMacro runs> DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"
else
<NoMacro runs> DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"
End if
Everything runs correctly and only generates the RMA form if a piece of equipment was marked for a RMA, but now for some reason, the Facilities form opens to the correct facility, but only 1 blank record is displayed in the subform vs showing all the records. If I close the Facilities form and reopen it, all tickets are displayed in the subform correctly.

All tickets should always be displayed in the subform whether a device was marked for RMA or not, the only thing this is doing is generating a RMA form when a device is selected for a RMA.

Any suggestions as this doesn't make any sense to me as nothing else changed other than changing from the varX=true to checking if the [RMA Transfer] = -1? If I put the varX stuff back in, the subform once again displays correctly


Last edited by NearImpossible; 07-17-2019 at 06:27 PM. Reason: Provided more details of whats happening
NearImpossible is offline   Reply With Quote
Old 07-17-2019, 02:25 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,825
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Form opens, but doesn't display correctly

Post was moderated. Now approved
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
NearImpossible (07-17-2019)
Old 07-17-2019, 08:08 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,095
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Form opens, but doesn't display correctly

Let's start with a simple test. Do you have Option Explicit declared at the top of each general and class module? If not, add it, then manually force Access to recompile your code - which it will happily do if you had edited even one line of code.

Now, I suspect something but from the small fragment of code you showed us, I can't verify it. So I'll ask you to get into that code, set a breakpoint, and hover the mouse over the parts I show below in red and in green.

Code:
If varX = True Then
You might have to set breakpoints and cycle through a few of these to get back more than one set of values for the varX definition. What I'm looking for is the two values in that IF statement when you expected a TRUE and when you expected a FALSE.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
NearImpossible (07-17-2019)
Old 07-17-2019, 09:56 PM   #4
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 34
Thanks: 39
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Form opens, but doesn't display correctly

I did not have Option Explicit, so that has been added

When I check the RMA Transfer, varX=-1 and True=True
When I uncheck the RMA Transfer, varX=-1 and True=True

Looking closer at the code, it is looking at the wrong table, so I changed the table from "dbo_nFacilityTicketEquipment" to "dbo_nFacilityEquipment" as in my second set of code,and the True statement should actually be False or it does the opposite of what it should, so I changed that as well. I now I get the following:

I ran 3 scenarios:
1. A New ticket with no equipment assigned
Ticket is created, but when the facilities form is opened, only 1 blank record is displayed
varX=0 and False=False
2. A new ticket with equipment assigned, and no RMA
Ticket is created, but when the facilities form is opened, only 1 blank record is displayed
varX=0 and False=False
3. A new ticket with equipment assigned, and identified as a RMA
Ticket is created, but when the facilities form is opened, only 1 blank record is displayed
varX=-1 and False=False
If I let the code run through, I am only getting the RMA form if the box is checked, but when the Facilities form opens, the subform only displays 1 blank record either way. If I close and reopen the Facilities form, all records are displayed correctly

The reason I am checking the dbo_nFacilityEquipment table is that is where the RMA Transfer is identified and the RMA Form generates if the RMA Transfer box is checked, the dbo_nFacilityTicketEquipment table is a secondary table tracking the history of tickets and RMAs per device

The "NoMacro" just creates the ticket in the dbo_nFacilityTickets table and clears the AddTicketForm
The "YesMacro" creates the ticket in the dbo_nFacilityTickets table , generates the RMA Form and then clears the AddTicketForm.

The Facility Form has a subform that displays all tickets for that facility from the dbo_nFacilityTickets table, which is even more confusing on why its not displaying correctly as all im doing is opening the facility form to display the facility based on the FacilityID

Last edited by NearImpossible; 07-17-2019 at 10:51 PM.
NearImpossible is offline   Reply With Quote
Old 07-17-2019, 09:57 PM   #5
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 34
Thanks: 39
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Form opens, but doesn't display correctly

reply pending mod approval...
NearImpossible is offline   Reply With Quote
Old 07-17-2019, 10:19 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,825
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Form opens, but doesn't display correctly

Now done. This is sometimes an issue when new members write lengthy posts with code or links
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
NearImpossible (07-17-2019)
Old 07-17-2019, 10:29 PM   #7
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 34
Thanks: 39
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Form opens, but doesn't display correctly

Quote:
Originally Posted by isladogs View Post
Now done. This is sometimes an issue when new members write lengthy posts with code or links
Understood and no problem, I just added that comment to let him know I had replied.

NearImpossible is offline   Reply With Quote
Old 07-17-2019, 11:18 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,825
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Form opens, but doesn't display correctly

That was a good idea.
Many new users just repost repeatedly when that happens and get frustrated when all get moderated!
Things usually settle down after the first ten posts which you've now reached
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
NearImpossible (07-19-2019)
Old 07-19-2019, 07:05 AM   #9
NearImpossible
Newly Registered User
 
Join Date: Jul 2019
Posts: 34
Thanks: 39
Thanked 0 Times in 0 Posts
NearImpossible is on a distinguished road
Re: Form opens, but doesn't display correctly

SO I am now getting the desired results from the If/Then statement, however the Facilities Ticket Subform is still not displaying all records for some reason when opening the Faclities form, so I have decided to forgo opening the Facilities Form and just going back to the main page.

I may revisit this issue later, however I have several other projects going on that requires more attention right now.

Thanks again for you assistance.

Kevin
NearImpossible is offline   Reply With Quote
Old 07-19-2019, 06:44 PM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,095
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Form opens, but doesn't display correctly

For what it is worth, the results of the test I asked were inconclusive.

Just to be pedantic about it, there is technically no difference between these two statements:

Code:
IF X = TRUE THEN
    some action
ELSE
    some other action
END IF

IF X THEN
    some action
ELSE
    some other action
END IF

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable doesn't store decimal value correctly aman Modules & VBA 2 10-02-2009 12:01 AM
Form display works and then doesn't mcdhappy80 Forms 1 09-17-2009 01:16 PM
Subform with query source doesn't display correctly puakenikeni Forms 1 07-09-2008 12:45 PM
[SOLVED] Form Doesn't Query Data Correctly SNewsome Forms 2 11-03-2004 10:16 AM




All times are GMT -8. The time now is 05:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World