Write unbound txt value to table

[ChronicFear]

I am in the process of creating a custom autonumber (I know, I know, it's not my choice. I'm just following orders). I've got it down so that the number works and increments every time someone opens a form to enter a new transaction. However, I could only get it to work if the text box is unbound. That is fine until I need to save all the data input on the form, including this autonumber. This is a silly question, but how do I tell the form to write to the table? I thought it would be something simple like Table![tblTransactions].[TransactionID] = Me.txtCustNum, but apparently not.

Any ideas? Or, if you see flaws in my logic, suggestions on a better way to approach this?

Thanks,
CF

Background info on the custom autonumber for those who want it:

When the new Transaction form opens, access looks at a table called tblCustAuto. In that table are three fields (key, autonum, and prefix). Access checks Prefix to see if it contains today's date formatted as yyyymd. If it matches, AutoNum is incremented by one. If no match, it changes Prefix to today's date and resets AutoNum to 1. Access then combines the Prefix and the AutoNum and puts them in the unbound textbox TransactionID on the Transaction form that is open.

This is all fine and dandy until I need to save. Hence, my above quesiton.

 

[WayneRyan]

CF,

I'm assuming that you have the "new" field on your form.

It doesn't have to be visible, but it should be there, WITH its
Control Source pointing to the correct field.

Then, invoke your code using the form's BeforeInsert event.

End your code with:

Me.TransactionID = Me.txtCustNum

Note:
====

You can't use syntax like --> Table![tblTransactions].[TransactionID] = Me.txtCustNum

Wayne

 

[ed_the_unlucky]

wayne, i'm experiencing the same issue as chronic fear. i'm an access novice and i don't know if CF is having this complication or not, but here goes:

b/c of the way i'm structuring my audit process, i would prefer the fields remain unbound, so that when a user presses a command button, only then are changes written to the table (more about that over in the general forum's audit trail thread).

am i making my life harder than i have to here?

 

[ChronicFear]

Wayne,

Thanks for the tip! The hidden textbox worked like a charm, although it kept clearing when it was in BeforeInsert. I solved the problem by putting it in Current and checking to see if the form was opened in add mode.

It's too bad I don't get to bill for each hour I spend hunting down the crazy things my boss wants me to build into his database.

Ed, what exactly seems to be your problem? Just getting it to write to the table? Wayne's suggestion worked well. If you're having other issues, feel free to give me a holler and maybe I can help you out.

-CF

 

[ChronicFear]

I need to bump this thread again:

So I thought everything was working perfectly, but now I have a random problem where I click the cmd button I use to open my Transactions form and the first time the form opens, the custom TransactionID field contians a zero. If I close the form and click to open it again, everything works fine. Any ideas. Code as follows:

Private Sub Form_Open(Cancel As Integer)
Dim olddate As Long
Dim newdate As Long
Dim OrgPrefix As Long
Dim S As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection

On Error Resume Next
    newdate = Date
    newdate = Format(newdate, "yyyymmdd")

' Check if this is the first transaction of the day
        S = "SELECT * FROM tblCustomAuto WHERE tblCustomAuto.Key = 1;"

        Set rs = New ADODB.Recordset
        rs.Open S, db, adOpenStatic, adLockOptimistic
        
        olddate = rs.Fields("Prefix")
        Set rs = Nothing
    
        If Not olddate = newdate Then
            S = "SELECT * FROM tblCustomAuto WHERE tblCustomAuto.Key = 1;"

            Set rs = New ADODB.Recordset
            rs.Open S, db, adOpenDynamic, adLockOptimistic
        
            rs.Fields("Prefix") = newdate
            rs.Fields("AutoNumber") = 1
            rs.Update
            Set rs = Nothing
            Set db = Nothing
        End If
    
'Get AutoNumber data from table
        S = "SELECT * FROM tblCustomAuto WHERE tblCustomAuto.Key = 1;"

        Set rs = New ADODB.Recordset
        rs.Open S, db, adOpenStatic, adLockOptimistic
        
        OrgPrefix = rs.Fields("Prefix")
        OrgIncrement = rs.Fields("AutoNumber")
        Set rs = Nothing
               
    Me.NewTransID = OrgPrefix & OrgIncrement

End Sub

and

Private Sub Form_Current()
    btnCancelPressed = False
    
    If Me.NewRecord = True Then
        Me.TransactionID = Me.NewTransID
    End If
End Sub

 

[ChronicFear]

Problem solved: I was setting db=nothing inside my if statement and then trying to use it later on. My bad.

 

[ChronicFear]

Thanks for the tip, Pat.