Pull Single Value from Table

[crhodus]

I know this is a simple thing to do but I am having a complete brain freeze.

I have a text box named txtTNo on my form. I also have a table named TrackIt that contains one field named TrackNumber. There is only one value in table TrackIt.

When the user creates a new record, I want to get this value from TrackIt.TrackNumber and add 1 to it. Then I want this "new" value to be displayed in txtTNo. After txtTNo has this "new" value, I want to save it to TrackIt.TrackNumber and have the old value overwritten.

Can anyone help me?

Thanks!

 

[dcx693]

On your form, bind the txtTNo field to the TrackNumber field on your TrackIt table. Use some code in the form's current event (or some other more appropriate event) to update the value of the txtTNo control on the form like this:

If IsNull(Me.txtTNo) Then
    Me.txtTNo=Dlookup("[TrackNumber]","TrackIt")+1
End If

 

[crhodus]

I finally figured out how to get the value from the table. Now I'm stuck on how to write this new vale that I have increased by one back to table TrackIt. Any Suggestions?

Dim varX As Double
varX = DLookup("[TrackNumber]", "TrackIt", "[TrackID] = 1")
varX = varX + 1
txtTNo = varx

 

[Late4Supper]

This is how a former associate did the same thing. tblProposalNumber only has 1 field and 1 value.


Dim MyDB As Database, MySet As Recordset
Dim PropNum As Long

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("tblProposalNumber")

PropNum = MySet!PropNum + 1
MySet.Edit
MySet.PropNum = PropNum
MySet.Update

MySet.Close
MyDB.Close

Me![GroupNum] = PropNum
Me.Refresh

 

[crhodus]

I tried using the code you provided Late4Supper, but I am receiving a "method or datamember not found" for the following code:

MySet.PropNum = PropNum


Im am assuming PropNum is the field that I want to update. I tried changing the name to the field in my table but I'm still having problems.

 

[dcx693]

Did anyone read my post from above?

 

[crhodus]

As soon as I post a message, I figure out how to get something to work. Anyway, here is the code I used (BTW I'm using Access 2000).
Also, I had to check Microsoft DAO x.x Object Library under Tools --> References to get everything to compile.

Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim PropNum As Double

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("TrackNo")

PropNum = MySet!TrackingNumber + 1
MySet.Edit
MySet.Fields("TrackingNumber") = PropNum
'''''MySet.TrackingNumber = PropNum
MySet.Update

MySet.Close
MyDB.Close


THANKS TO EVERYONE HELPING ME ON THIS!

 

[Late4Supper]

Can you send me your code? It runs under A97 with the following references

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

 

[crhodus]

Here is the code I used. I probably don't have to have the WHILE statement in the code, since there will only be 1 record in this table, but I thought I'd put in in there just in case another record was added by accident.
'-------------------------------------------
Private Sub SaveTrackingNumber(ByVal ThisNumber As Double)
'-------------------------
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim PropNum As Double
'-------------------------

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("TrackNo")

PropNum = ThisNumber

With MySet
MySet.MoveFirst

While MySet.Fields("TrackID") <> 1
MySet.MoveNext
Wend

MySet.Edit
MySet.Fields("TrackingNumber") = PropNum
MySet.Update

End With

MySet.Close
MyDB.Close

End Sub

'-------------------------------------------
Here is a list of all the references I have checked:
Visual Basic for Applications
MS Access 9.0 Object Library
OLE Automation
MS ActiveX Data Objects 2.1 Library
AxPrf ActiveX Control module
MS DAO 3.6 Obj. Library

 

[crhodus]

Did anyone read my post from above?

Hi dcx693,

I read your post right after I posted a message. I ended up figuring out something that was very similar to what you suggested.

Thanks!

crhodus