Type Mismatch When Using "On Dbl Click" To Open Form

[PaultheITGuy]

Hi,

I'm working on a database to keep a track of songs used at events, for copyright purposes.

On the main entry page I have a subform (AuthorSub) which records the writer of the song. If the author is not in the drop down list, it lets you double click on the record (Author) to open a new form (also called Author), where the name of the author can be entered. When you exit the form it requeries the subform and the drop down then includes the new writer's name.

However, I noticed today this only works if you double click an empty record. If you double click a populated record then you get the error 'Type Mismatch'

Can anyone explain why this is, and maybe help me fix it?

Many thanks in advance.

Here is the VBA for the On Dbl Click Event:

Private Sub Author_DblClick(Cancel As Integer)
On Error GoTo Err_Author_DblClick
Dim lngAuthor As Long

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

Exit_Author_DblClick:
Exit Sub

Err_Author_DblClick:
MsgBox Err.Description
Resume Exit_Author_DblClick
End Sub

 

[KiaruB]

hey,

your variable lngAuthor is a long and u are trying to put text into it...
declare your lngAuthor as string or variant

error : lngAuthor = Me![Author]

 

[TheAceMan1]

How are ya PaultheITGuy . . .

What does the [blue]Bound Column[/blue] property show for the combobox?

Post the [blue]RowSource[/blue] property as well.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PaultheITGuy]

Hi Guys - thanks for the replies...

The Row Source in the subform is a query, which gets its data from a table called Author with the two editable fields (the other is an autonumber ID field) called FirstName and LastName - here is the query:

SELECT ([Firstname]+" ") & [Lastname] AS Expr1 FROM Author ORDER BY Author.Lastname, Author.Firstname;

If you can suggest to me how to fix this I would be most grateful as I'm not very knowledgable in VBA.

Thanks

 

[TheAceMan1]

PaultheITGuy . . .

I intend to redirect you to the [blue]On Not in List[/blue] event of the combo, but I need to know if in form [blue]Author[/blue] your only appending First & Last name?

Typically a form is opened to do this if you need to append additional data such as address, city, state ... Otherwise in can be done directly with an [blue]append SQL[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]