Adding a new record to an Access table Through a form

[haneen97]

Trying to add a new record in an Access table through a form. I have an add button on it. I would like to automate populating the next sequencial number in a filed. I can't use autonumber since the data was converted and field had data already. I careated this function to get the next number:

Function NewTrackNum()
Dim sfNum As Integer
Dim SqlStr As String

SqlStr = ("SELECT max(SF_Tracking_Num + 1 FROM Tempus_Issues_Log)&quot
NewTrackNum = SqlStr

End Function

In the add button click, I have this line to populate the vaule.

Me.SF_Tracking_Num = NewTrackNum()

When I click the add button, I got this error
(The value you entered isn't valid for this field).

Please help, where did I go wrong.

Thanks

 

[jebry]

Hi!

Try this:

Function NewTrackNum()
Dim sfNum As Integer
Dim SqlStr As String
Dim rst As DAO.Recordset

SqlStr = ("SELECT max(SF_Tracking_Num) As MaxNum FROM Tempus_Issues_Log)&quot
Set rst = CurrentDb.OpenRecordset(SqlStr, dbOpenDynaset)
sfNum = rst!MaxNum + 1
NewTrackNum = sfNum
Set rst = Nothing

End Function


hth
Jeff Bridgham
bridgham@purdue.edu

 

[haneen97]

Hi Jeff,

I got a compiler error on this line
(User-Defined type not defined)

Dim rst As DAO.Recordset

It sounds like I am missing a componant to add. Do you have an idea what it is and how can I add it?

Thanks a lot

Mo

 

[jebry]

Hi Mo!

Yes, when you are in the VB screen go to Tool-References. Find the reference to Microsoft Data Access Objects 3.6 and check the box beside it and say okay.

There is another error in the code that I missed before. In the line that says SqlStr = etc, you need to delete the paretheses.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[haneen97]

This passed that error, I got another error, I will tell you about later. I have to go to a meeting.

Mo

 

[haneen97]

Hi Jeff,

The error is a run time error,

(Syntax error in FROM clause)

It heighlited this line,

Set rst = CurrentDb.OpenRecordset(SqlStr, dbOpenDynaset)

What do you think?

Thanks

Mo

 

[jebry]

Hi!

Your SQL should look like this:

SqlStr = "SELECT max(SF_Tracking_Num) As MaxNum FROM Tempus_Issues_Log"

Assuming everything is spelled correctly this should open up the recordset.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[haneen97]

YESSSSS,

It worked, thanks a lot Jeff. I appreciate your help.

Mo