Error with ‘open.args’ code (2000)

Topic

New Reply

Sigh, OK, I’ve been on before with this same issue. I’ve gone back to my post a few weeks ago to re-trace my steps to try to find the issue but can’t.

Problem: I’m opening frmBilling from a name in lstIndividualBillingName. frmBilling opens to a new record that should correspond the AccountID in lstIndividualBilling Name. I’ve used the following on the load event for frmBilling:

Me.AccountID.DefaultValue = Me.OpenArgs

The following code opens frmBilling when lstIndividualBillingName is double clicked:

DoCmd.OpenForm FormName:=”frmBilling”, _
WhereCondition:=”AccountID = ” & Me.lstIndividualBillingName

frmBilling has qryBilling as a control source.

The error message I get is “Invalid Use of Null”, with “Me.AccountID.DefaultValue = Me.OpenArgs” hightlighted.

Does the fact that the control source is a query vs the table have anything to do with the problem? The SQL for the query is:

SELECT tblDemographics.AccountID, tblDemographics.[LastName/BusinessName], [salutation] & ” ” & [firstname] & ” ” & [middleinitial] & ” ” & [lastname/businessname] & ” ” & [suffix] AS Expr1, tblAccountsReceivable.DateWorked, tblAccountsReceivable.HoursWorked, tblAccountsReceivable.LaborCharge, tblAccountsReceivable.PreDiscountCharge, tblAccountsReceivable.Discount, tblAccountsReceivable.PostDiscountCharge, tblAccountsReceivable.SalesTax, tblAccountsReceivable.PostSalesTaxCharge, tblAccountsReceivable.DatePaid, tblAccountsReceivable.AmtPaid, tblAccountsReceivable.Balence, tblAccountsReceivable.CreditAmountDue, tblAccountsReceivable.RebillAmt, tblAccountsReceivable.PaidInFull, tblAccountsReceivable.PartialPayment, tblAccountsReceivable.Rebill, tblAccountsReceivable.CreditDue, tblAccountsReceivable.PaymentSource, tblAccountsReceivable.InvoiceComments, tblDemographics.TaxExemptStatus, tblDemographics.ProspectDate, tblDemographics.ReactivateDate, tblDemographics.InactiveDate, tblDemographics.ActivationDate, tblDemographics.Active
FROM tblDemographics RIGHT JOIN tblAccountsReceivable ON tblDemographics.AccountID = tblAccountsReceivable.AccountID
ORDER BY tblDemographics.[LastName/BusinessName];

The query does allow records to be added.

Thanks,
Leesha

Reply | Quote

Replies

Replace:
Me.AccountID.DefaultValue = Me.OpenArgs
with:
If Not IsNull(Me.OpenArgs) Then Me.AccountID.DefaultValue = Me.OpenArgs

Reply | Quote

Cool!! That did it!

Thanks!
Leesha

Reply | Quote

OK, I went to test the form and what I found is that the record initially opens to the name chose on frmBillingAccountName and it is a new record. When I enter data into the record, save, exit off the form and go back to frmBillingAccountName and select the same Account as previously used, the form enters the record I just created.

I went back and checked my code and noted it didn’t contain the code to open the form as new so I changed the code on the doubleclick command on frmBillingAcccountName to:

DoCmd.OpenForm “frmBilling”, acNormal, , “AccountID” & Me.lstIndividualBillingName, acFormAdd

Now when I doubleclick the name on the list on frmBillingAccountName to open frmBilling, frmBilling opens to a new record but the corresponding AccountID is no longer selected. I need frmBilling to open to a new record with the AccountID from the list on frmBillingAccountName being the one that frmBilling opens to.

Thanks,
Alicia

Reply | Quote

Maybe this is a silly question but if you want to enter another record for the same accountID, why are you going back to the first form to do it? You should be able to do it immediately and the default value will still be in place.

Reply | Quote

Hi Charlotte,

No it isn’t a silly question at all. The problem is that when the user initally goes to frmBilling to enter the data they need to create an invoice, they select the name of the Account from frmBillingAccountName. After doubleclicking on this name, frmBilling needs to open up to a new record in order for them to create an invoice, with the name of the Account already present (selected from frmBillingAccountName). The reason they do it this way is to ensure that the Account being billed to has had their demographic information entered before the invoice is created. If the Account Name is not on the list then the are not able to access frmBilling. They must first enter the demographic informatin into frmDemographics. Once on frmBilling, if they needed to create more than one invoice for an Account they will (haven’t put that in yet) have the ability to open a new record directly from the form. I’m sure that will blow up on me when I add that command button and I’ll be back for info on that to be sure the new record opens to the same AccountID!

Leesha

Reply | Quote

Your command to open a form at a specific account:
DoCmd.OpenForm “frmBilling”, acNormal, , “AccountID” & Me.lstIndividualBillingName, acFormAdd

should read:
DoCmd.OpenForm “frmBilling”, acNormal, , “AccountID=” & Me.lstIndividualBillingName, acFormAdd

However, if AccountID is text, then try:
DoCmd.OpenForm “frmBilling”, acNormal, , “AccountID=” & Chr(34) & Me.lstIndividualBillingName & Chr(34), acFormAdd

Reply | Quote

Hi Pat,

I copied and pasted the code in and frmBilling still opens to new record (which is what I want it to do) however the name of the account that corresponds to the one selected in lstIndividualBillingName is not selected. It opens to everything as blank/new.

Leesha

Reply | Quote

If I may jump in again here, you are not passing an OpenArgs argument at the moment. Try

DoCmd.OpenForm “frmBilling”, acNormal, , “AccountID=” & Me.lstIndividualBillingName, acFormAdd, , Me.lstIndividualBillingName

Reply | Quote

Hi,

I tried the code you suggested and get an error message that says “An expression that you entered is the wrong data type …….”.

I tried placing a command button on frmDemographics (which would already contain the AccountID etc.) that would allow the user to create an invoice. In addition I created an identical form to frmBilling called frmInvoice in an effort to not mess up any code you or Pat or others may suggest. This is the code I place behind the control:

DoCmd.OpenForm “frmInvoice”, , , , acFormAdd, , Me.[AccountID]

The load code on frmInvoice (duplicate of frmBilling) is:

Me.AccountID = Me.OpenArgs

For whatever reason that I don’t understand, the code worked and the form opened to the the AccountID that is frmDemographics. I then made a copy of frmBillingAccountName, calling frmInvoiceAccountName and inserted the same code ……………….DoCmd.OpenForm “frmInvoice”, , , , acFormAdd, , Me.[AccountID]………………and it works. I of course have no idea why.

Leesha

Reply | Quote

In that case you should have been using Me.AccountID all along instead of Me.lstIndividualBillingName…

Reply | Quote

When you say:
>>however the name of the account that corresponds to the one selected in lstIndividualBillingName is not selected. It opens to everything as blank/new.<<
Do you want the name of the account to be automatically entered into one of the controls for the new record, if so you will need an OpenArgs argument of the OpenForm statement, like:
DoCmd.OpenForm "frmBilling", acNormal, , , acFormAdd, , Me.lstIndividualBillingName

The open form that I suggested should open the form at the suggested record, as this uses the Criteria argument of the openform statement.

Reply | Quote

Hi,

Yes, I need the form to open with AccountID automatically entered. I just posted to Hans a minute ago with what I found that works. Of course I don’t know why it worked, but it did!

Thanks,
Leesha

Reply | Quote