I need suggestions dealing with "Invalid Use of Null" Error 94. 1

[Johnnycat1]

Hello all,

I am trying to use the following code to populate a table that I will use later to create & manage lot totals.

The problem that I have is when the cell in the form that is creating the name for the lot total is null the "Invalid Use Of Null" error comes up. Some line items in the form will be part of a lot price and others will be a unit price item and subsequently the LotPriceName would be null.

The following is the code that I have used to populate the lot totals table it works great except when "Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform!PPFixtureLotPriceName" is null.


Private Sub Lot_Price_Name_Exit(Cancel As Integer)

Dim strPPFixtureLotID As String
Dim strLotPriceName As String
Dim strPPID As String
Dim rstLotPriceName As DAO.Recordset

strPPFixtureLotID = Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform![Project Name] & "-" & Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform!PPFixtureLotPriceName

strLotPriceName = Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform!PPFixtureLotPriceName

strPPID = Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform!PPID

'Add PPFixtureLotID In TblProjectPricingFixuresLotPrices Table

Set rstLotPriceName = CurrentDb.OpenRecordset("TblProjectPricingFixturesLotPrices", dbOpenDynaset)

With rstLotPriceName
.FindFirst "[PPFixtureLotID]='" & strPPFixtureLotID & "'"
If .NoMatch Then .AddNew Else Exit Sub
![PPFixtureLotID] = strPPFixtureLotID

'Add PPFixtureLotPriceName In TblProjectPricingFixuresLotPrices Table
![PPFixtureLotPriceName] = strLotPriceName

'Add PPID In TblProjectPricingFixuresLotPrices Table
![PPID] = strPPID
.Update

End With



End Sub

Any suggestions???

 

[PHV]

replace this:
Dim strLotPriceName As String
with this:
Dim strLotPriceName As Variant

You may have to play with the IsNull function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya Johnnycat1 . . .

. . . and this for starters . . .

[blue]Private Sub Lot_Price_Name_Exit(Cancel As Integer)
   Dim frm As Form, rst As DAO.Recordset
   
   Set sfrm = Forms!FrmProjectPricing!FrmProjectPricingFixturesSubform.Form
   Set rst = CurrentDb.OpenRecordset("TblProjectPricingFixturesLotPrices", dbOpenDynaset)
   
   With rst
      .FindFirst "[PPFixtureLotID]='" & sfrm!PPID & "'"
      
      If .NoMatch Then
         .AddNew
            ![PPFixtureLotID] = sfrm![Project Name] & "-" & sfrm!PPFixtureLotPriceName
            ![PPFixtureLotPriceName] = sfrm!PPFixtureLotPriceName
            ![PPID] = sfrm!PPID
         .Update
      End If
   End With
   
   Set rst = Nothing

End Sub[/blue]

See Ya! . . . . . .