Incrementing using a recordset. 1

[ammujr]

I have 2 tables one that is called Counter table: 1 row 1 column that has a numeric value in it. The second table is called and EC my purchase order number/information in it.

Here is what I am trying to do (if it is possible)


So the user will push the add record button and the purchase order number will be the present number stored in the counter table (then the table increments) and the rest of the fields blank. Then i want to be able to fill out all the details (date, customer name, address, city state, number) and save that information in the purchase order table. I have module that increments the counter tableafter a button is clicked, so no two P.O numbers will be the same. I did this bc I am in multiuser enviroments so autonumber kept giving me problems.

Hopefully I am making sense. Thanks in advance!!!


JIll

 

[dyarwood]

You could use something like

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open "CounterTABLE",cnn
numb = rs.Fields(0)

strSQL = "UPDATE PURCHASETABLE" & vbCrLf
strSQL = strSQL & "VALUES '" & numb "'"
cnn.Execute strSQL

something like that should update take the number in the counter table and then update the purchase table with that number in the first column.

Let me know if that helps

dyarwood

 

[MakeItSo]

HI Jill!

dyarwood's approach is OK if you use ADO reference.
But: wouldn't you need an INSERT INTO statement instead of an Update? You want to add a record, don't you? Then it's

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open "CounterTABLE",cnn
numb = rs.Fields(0)

strSQL = "INSERT INTO Purchasetable (ID)" & vbCrLf
strSQL = strSQL & "VALUES '" & numb "'"
cnn.Execute strSQL

with DAO recordsets:

Dim sSQL as String, rs as Recordset, numb
Set rs=currentdb.Openrecordset "CounterTABLE"
rs.MoveLast (If you store more than one line...)
numb = rs.Fields(0)

sSQL="INSERT INTO Purchasetable (ID) VALUES (" & numb & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

Happy new year to all of you! ;-)
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[dyarwood]

You are probably right about the INSERT INTO statement. Still on the learning curve with SQL.

 

[ammujr]

Hi guys,

thanks for helping me. But I am little confused. I have a form the user uses to add a record. So when the form is loaded, I want the counter table present value in the field and to then to increment the counter. Then I want the user to fill out the form with the rest of the information.

So from the switch board i would press a button that would pull up the "add purchase order" form. all the fields would be blank except the P.O. Number. That field would be locked and be the present value in the counter table and after it insert it would then increment the number, so other user can use it. I know how to increment the counter but not how to put the value in the text box.

Then once all the information is filled out I would have the user press save, and in the event procedure i would just have an ON_CLICk sub that does the SQL for the insert statements.

But presently when i load the form it put the increment value but associates it to the present records and so i can't save or edit the information.

Thank you!!!
Jill