DblClick error and Cascading Combobox

[Mark]

Am new to access, no nothing of code and really need some help. Ive got 2
problems, i hope someone can help with (sorry if putting 2 in one thread is
bad form)

the first is with a DblClick Type Mismatch Error. Ive got the following code

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

If the ChemicalId field is empty the dblclick work, ie opens the form. But
if there is something already in there i get the type mismatch error. Any
suggestions on how to change the code???

2nd question: ive got a cascading combo box that limits the values
dependent on the choice in another combo. This choice is limited to just one
option so it doesnt need to be a combo, a list box just displaying the value
would be better. with it being a combo i have to choose the single option
and it then gets put in to my table. The problem is if i make it a list box,
it displays fine on the form but because im not selecting anything it doesnt
get put in to the table. what do i need to change so that a value gets put
in to the table without being selected but just by being displayed. hope
that makes some sort of sense. help greatly appreciated

 

[TonyT]

Hi Mark,

replies in order below;

Am new to access, no nothing of code and really need some help. Ive got 2
problems, i hope someone can help with (sorry if putting 2 in one thread is
bad form)

the first is with a DblClick Type Mismatch Error. Ive got the following code

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

If Me.ChemicalID is a bound field you can't set it to null (if it's a
required or PK field), I assume that the new form "chemicals" checks the
value of the current forms ChemicalID field to determine what it displays? If
so probably better to use a global variable & check against that, and leave
the ChemicalID field un-edited.

If the ChemicalId field is empty the dblclick work, ie opens the form. But
if there is something already in there i get the type mismatch error. Any
suggestions on how to change the code???

2nd question: ive got a cascading combo box that limits the values
dependent on the choice in another combo. This choice is limited to just one
option so it doesnt need to be a combo, a list box just displaying the value
would be better. with it being a combo i have to choose the single option
and it then gets put in to my table. The problem is if i make it a list box,
it displays fine on the form but because im not selecting anything it doesnt
get put in to the table. what do i need to change so that a value gets put
in to the table without being selected but just by being displayed. hope
that makes some sort of sense. help greatly appreciated

Why not just use a bound textbox instead of a list or combo box?

TonyT..