result is blank page

[Guest]

I've been having a problem with results coming up with a blank page in my
form. For example, if I have an account number and I want to see how much of
the account balance is paid, I have a parameter query where I can enter the
account # in a box, and a form that displays the paid status among other
information. I have a table for account details, and a separate table for pay
status.

All goes well unless there is an account where the paid amount is not listed
in the pay status table (blank, not zero), or for some reason the account is
not listed in the pay status table at all. Then the entire result is a blank
page, rather than having just the "pay status" field on my form display
nothing. How can I get the result to come up, but with a message indicating
that the pay status is missing rather than just a complete blank page?

Dino

 

[Guest]

Dino:

Join the accounts and pay status tables by means of an outer join in the
query on which your form is based, e.g.

SELECT Accounts.AccountNumber, AccountName, PaidAmount
FROM Accounts LEFT JOIN PayStatus
ON Accounts.AccountNumber = PayStatus.AccountNumber
WHERE Accounts.AccountNumber = [Enter Account Number:];

If you want to alert the user to the missing pay status then put some code
in the form's Current event procedure e.g.

Const conMESSAGE = "No current pay status record(s) for this account."
Dim strCriteria As String

strCriteria = "AccountNumber = " & Me.AccountNumber

If IsNull(DLookup("AccountNumber", "PayStatus", strCriteria)) Then
MsgBox conMESSAGE, vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

 

[Guest]

Thank you, I will try it.

Dino:

Join the accounts and pay status tables by means of an outer join in the
query on which your form is based, e.g.

SELECT Accounts.AccountNumber, AccountName, PaidAmount
FROM Accounts LEFT JOIN PayStatus
ON Accounts.AccountNumber = PayStatus.AccountNumber
WHERE Accounts.AccountNumber = [Enter Account Number:];

If you want to alert the user to the missing pay status then put some code
in the form's Current event procedure e.g.

Const conMESSAGE = "No current pay status record(s) for this account."
Dim strCriteria As String

strCriteria = "AccountNumber = " & Me.AccountNumber

If IsNull(DLookup("AccountNumber", "PayStatus", strCriteria)) Then
MsgBox conMESSAGE, vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

I've been having a problem with results coming up with a blank page in my
form. For example, if I have an account number and I want to see how much of
the account balance is paid, I have a parameter query where I can enter the
account # in a box, and a form that displays the paid status among other
information. I have a table for account details, and a separate table for pay
status.

All goes well unless there is an account where the paid amount is not listed
in the pay status table (blank, not zero), or for some reason the account is
not listed in the pay status table at all. Then the entire result is a blank
page, rather than having just the "pay status" field on my form display
nothing. How can I get the result to come up, but with a message indicating
that the pay status is missing rather than just a complete blank page?

Dino

 

[Guest]

Ken,
The "LEFT JOIN" worked great for having the form come up even though the
paid field might be missing, but I'm having trouble with the message box. It
seems like I entered in everything exactly like you wrote, substituting my
own values for the names of the tables and fields, but the result is:

If the AccountNumber is not found, the blank screen comes up. If the
AccountNumber IS found the form comes up like it should, but the error
message box also comes up. I double-checked my typing, what could I be doing
wrong?
Thanks for your help,
Dino

Dino:

Join the accounts and pay status tables by means of an outer join in the
query on which your form is based, e.g.

SELECT Accounts.AccountNumber, AccountName, PaidAmount
FROM Accounts LEFT JOIN PayStatus
ON Accounts.AccountNumber = PayStatus.AccountNumber
WHERE Accounts.AccountNumber = [Enter Account Number:];

If you want to alert the user to the missing pay status then put some code
in the form's Current event procedure e.g.

Const conMESSAGE = "No current pay status record(s) for this account."
Dim strCriteria As String

strCriteria = "AccountNumber = " & Me.AccountNumber

If IsNull(DLookup("AccountNumber", "PayStatus", strCriteria)) Then
MsgBox conMESSAGE, vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

I've been having a problem with results coming up with a blank page in my
form. For example, if I have an account number and I want to see how much of
the account balance is paid, I have a parameter query where I can enter the
account # in a box, and a form that displays the paid status among other
information. I have a table for account details, and a separate table for pay
status.

All goes well unless there is an account where the paid amount is not listed
in the pay status table (blank, not zero), or for some reason the account is
not listed in the pay status table at all. Then the entire result is a blank
page, rather than having just the "pay status" field on my form display
nothing. How can I get the result to come up, but with a message indicating
that the pay status is missing rather than just a complete blank page?

Dino