Form opens, but doesn't display correctly

[NearImpossible]

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

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

If varX = True Then
[INDENT]<NoMacro runs>
DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"[/INDENT]
else
[INDENT]<YesMacro runs>
DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"[/INDENT]
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

If DLookup("[RMA Transfer]", "dbo_nFacilityEquipment", "[TicketID]= " & Me.TicketID) = "-1" Then
[INDENT]<YesMacro runs>
DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"[/INDENT]
else
[INDENT]<NoMacro runs>
DoCmd.OpenForm "Facilities", , , "[FacilityID]= " & Me.[FacilityID], , , "ticket"[/INDENT]
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

 

[isladogs]

Post was moderated. Now approved

 

[The_Doc_Man]

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.

If [COLOR="Red"]varX[/COLOR] = [COLOR="SeaGreen"]True[/COLOR] 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.

 

[NearImpossible]

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

 

[NearImpossible]

reply pending mod approval...

 

[isladogs]

Now done. This is sometimes an issue when new members write lengthy posts with code or links

 

[NearImpossible]

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.

 

[isladogs]

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

 

[NearImpossible]

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

 

[The_Doc_Man]

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:

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

IF X THEN
    some action
ELSE
    some other action
END IF