Problem adding new records with an On Current event

[SOMSteve]

I am using the "On Current" and "After Update" events on a form to display select fields, depending on what type of transfer the user selects. This works fine up until I go to add a new record, at that point I get a "Run-Time Error '13' Type Mismatch" error message. I've looked into this a little bit and have not been able to get past it. I tried following the advice given in the FAQ

http://www.tek-tips.com/faqs.cfm?fid=946

but that didn't seem to help. Any ideas on what I'm doing wrong?

I'm running Microsoft Access 2000 (SP-3). When the error message pops up and I go to debug it points to the very first line of the code (the one that starts with Me!cboLiveAuctionDate.Visible).

Private Sub doFieldsVisibility()
    'Make visible the items for transfers to Live Auctions
    Me!cboLiveAuctionDate.Visible = (Me!TransferType = "Live Auction")
    Me!LiveAuctionDate_Label.Visible = (Me!TransferType = "Live Auction")
    'Make visible the items for transfer to other agencies
    Me!cboToAgency.Visible = (Me!TransferType = "Agency")
    Me!ToAgency_Label.Visible = (Me!TransferType = "Agency")
    'Make visible the items for transfers to Internet Sales)
    Me!cboMiBidLotNum.Visible = (Me!TransferType = "Internet")
    Me!MiBidLotNum_Label.Visible = (Me!TransferType = "Internet")
    Me!txtOnlineDate.Visible = (Me!TransferType = "Internet")
    Me!OnlineDate_Label.Visible = (Me!TransferType = "Internet")
    Me!txtAuctionCloseDate.Visible = (Me!TransferType = "Internet")
    Me!AuctionCloseDate_Label.Visible = (Me!TransferType = "Internet")
    'Make visible the items for transfers to Sealed Bid
    Me!cboSealedBidNum.Visible = (Me!TransferType = "Sealed Bid")
    Me!SealedBidNum_Label.Visible = (Me!TransferType = "Sealed Bid")
    Me!cboWinningBidder.Visible = (Me!TransferType = "Sealed Bid")
    Me!WinningBidder_Label.Visible = (Me!TransferType = "Sealed Bid")
    Me!txtDescription.Visible = (Me!TransferType = "Sealed Bid")
    Me!Description_Label.Visible = (Me!TransferType = "Sealed Bid")
    Me!txtWinningAmount.Visible = (Me!TransferType = "Sealed Bid")
    Me!WinningAmount_Label.Visible = (Me!TransferType = "Sealed Bid")
End Sub

Private Sub Form_Current()
    doFieldsVisibility
End Sub

Private Sub TransferType_AfterUpdate()
    doFieldsVisibility
End Sub

Thanks for the help, let me know if I can tell you anything else to be of assistance.

-Steve

 

[PHV]

Perhaps this crude workaround ?
Private Sub Form_Current()
[!]If Not Me.NewRecord Then [/!]doFieldsVisibility
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya SOMSteve . . .

The error . . .

[blue]"Run-Time Error '13' Type Mismatch"[/blue]

. . . occurs because [blue]TransferType[/blue] is a [purple]Null[/purple] (not an empty string . . . new record or not!). Your [blue]comparing strings[/blue] here!

To fix this and cut down the code, perform the following:
[ol][li]In [blue]form design view[/blue], in the [blue]Tag property[/blue] of the fields you've prescribed, enter the proper text: [blue]Agency[/blue] or [blue]Internet[/blue] or [blue]Live Auction[/blue] or [blue]Sealed Bid[/blue] (no quotations please).[/li]
[li]Backup your [blue]doFieldsVisibility[/blue] routine then copy/paste replacing it with the following:

[blue]   Dim ctl As Control, Detect As String
   
   Detect = "AgencyInternetLive AuctionSealed Bid"
   
   For Each ctl In Me.Controls
      If ctl.Tag <> "" Then
         If InStr(1, Detect, ctl.Tag) > 0 Then
            Me(ctl.Name).Visible = (Nz(Me!TransferType, "") = ctl.Tag)
         End If
      End If
   Next[/blue]

[/li][/ol]
[blue]Let me know how ya make out![/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[SOMSteve]

Thanks for the advice PHV and TheAceMan1! Given a time crunch to get this project done PHV's workaround fixed it quickly. In the future I'll use the method you recommended TheAceMan1.

Thanks again to both of you!

-Steve