DLookup Run time error question

[Renee]

Good Morning all,

I have the following code on the After Update event of a combo box:

Private Sub biennium_AfterUpdate()

Dim varBiStart As Variant
Dim varBiEnd As Variant

If Not IsNull(Me.biennium) Then
varBiStart = DLookup("[beinnium_start]", "tbl_Bienniums", "[Auto_ID] = "
& Me.biennium)
varBiEnd = DLookup("[biennium_end]", "tbl_bienniums", "[Auto_ID] = " &
Me.biennium)
If Not IsNull(varBiStart) Then
Me.start_date = varBiStart
If Not IsNull(varBiEnd) Then
Me.end_date = varBiEnd
End If
End If
End If


End Sub

I am getting a runtime error 2001, stating I have canceled the previous
operation. I don't know what 'operation' it could be referring to. I have
default values on the start_date and end_date, but I don't see why I can't
update that value based on the biennium selection.

Thank you in advance for your support!
Renee

 

[Rick Brandt]

Good Morning all,

I have the following code on the After Update event of a combo
box:

Private Sub biennium_AfterUpdate()

Dim varBiStart As Variant
Dim varBiEnd As Variant

If Not IsNull(Me.biennium) Then
varBiStart = DLookup("[beinnium_start]", "tbl_Bienniums",
"[Auto_ID] = " & Me.biennium)
varBiEnd = DLookup("[biennium_end]", "tbl_bienniums", "[Auto_ID]
= " & Me.biennium)
If Not IsNull(varBiStart) Then
Me.start_date = varBiStart
If Not IsNull(varBiEnd) Then
Me.end_date = varBiEnd
End If
End If
End If


End Sub

I am getting a runtime error 2001, stating I have canceled the
previous operation. I don't know what 'operation' it could be
referring to. I have default values on the start_date and end_date,
but I don't see why I can't update that value based on the biennium
selection.

Thank you in advance for your support!
Renee

I'm not really sure what is causing your error but it looks like you are
selecting a value in your ComboBox that comes from the table tbl_bienniums
and then using Dlookup to go and grab two additional fields from the same
row in that same table. If that is correct then just add those as hidden
columns in your ComboBox Rowsource. Then the DLookups can be eliminated.
All you need to do is refer to the additional columns as...

Me.start_date = Me.biennium.Column(1)
Me.end_date = Me.biennium.Column(2)

The column numbers start with zero so the above is grabbing the second and
third columns. You would need to alter the RowSource of the ComboBox to
include the additional columns, Change the ColumnCount property to 3, and
set the ColumnWidths so the second and third columns have a zero width.

 

[Renee]

Great! Thank you Rick.

Good Morning all,

I have the following code on the After Update event of a combo
box:

Private Sub biennium_AfterUpdate()

Dim varBiStart As Variant
Dim varBiEnd As Variant

If Not IsNull(Me.biennium) Then
varBiStart = DLookup("[beinnium_start]", "tbl_Bienniums",
"[Auto_ID] = " & Me.biennium)
varBiEnd = DLookup("[biennium_end]", "tbl_bienniums", "[Auto_ID]
= " & Me.biennium)
If Not IsNull(varBiStart) Then
Me.start_date = varBiStart
If Not IsNull(varBiEnd) Then
Me.end_date = varBiEnd
End If
End If
End If


End Sub

I am getting a runtime error 2001, stating I have canceled the
previous operation. I don't know what 'operation' it could be
referring to. I have default values on the start_date and end_date,
but I don't see why I can't update that value based on the biennium
selection.

Thank you in advance for your support!
Renee

I'm not really sure what is causing your error but it looks like you are
selecting a value in your ComboBox that comes from the table tbl_bienniums
and then using Dlookup to go and grab two additional fields from the same
row in that same table. If that is correct then just add those as hidden
columns in your ComboBox Rowsource. Then the DLookups can be eliminated.
All you need to do is refer to the additional columns as...

Me.start_date = Me.biennium.Column(1)
Me.end_date = Me.biennium.Column(2)

The column numbers start with zero so the above is grabbing the second and
third columns. You would need to alter the RowSource of the ComboBox to
include the additional columns, Change the ColumnCount property to 3, and
set the ColumnWidths so the second and third columns have a zero width.