The wrong way of doing things... temptables

[drkhelmt]

OK, I know this is probably the disasterously wrong way of doing it, so I need a pointer in the right direction.... Everything works, but not well. (I know, if it ain't broke....)

I have a form to collect data about invoices recieved. This form is in datasheet and has the following open event:

Dim dbs As Database, sqlst As String

'create temp table for the batch to be saved to
Set dbs = CurrentDb
dbs.Execute "CREATE TABLE tempInvTable " _
    & "(poID INTEGER, invDate DATETIME, invNum CHAR, invAmt DOUBLE, vname TEXT, discount DOUBLE, dTime INTEGER, net INTEGER, complete YESNO);"

'change the form's record source to the temp table
Me.RecordSource = "tempInvTable"
dbs.Close

Here's the problem, when I enter an invoice number, the folowing lost focus happens:

Private Sub invNumber_txt_LostFocus()
Me.invNumber_txt = Trim(Me.invNumber_txt)
If Not Me.invNumber_txt = "" Then
    If DCount("invNumber", "invoice", "[invNumber] = '" & Me.invNumber_txt & "'") > 0 Then
        MsgBox "This invoice number already exists.  Please confirm this is OK"
    End If
End If
End Sub

This checks the permanent table of invoices to try and catch duplicate invoices, however when it does, it adds trailing spaces to the invoice number. The question is, how do I prevent the trailing spaces?

I know Access03 can do temptables, however when I origionally wrote the code, I got stumped on them and never figured it out. Any help on how I change this to make it better is appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[drkhelmt]

Oh, almost forgot to mention... when the form is closed, the tempInvTable contents is copied to the permanent invoice table and the tempInvTable is deleted making the tempInvTable temporary.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?