Database

[maggieot]

I have a program that allows users to enter data through a list box and text boxes. The data is then saved to an access database. The primary key in the database is "user ID" and, when I shut down and re-run the program, "user ID" gets overwritten.
How do I have the program go to the end of the database to save the next record?

[Lethal]

What type of control are you using, ADO or DAO. Are you using the .AddNew method??

[maggieot]

ADO

yes .addnew

[Lethal]

It would be easier to answer if you could post some code. The use of the addNew method should add a blank record at the end of the recordset.

[maggieot]

Ok - this is going to make no sense at all (sorry)...I'm working w/Robb and we're all messed up.

The point is, when we run the program, it accepts our input and saves it to the database, but, when we close out and then re-run the program, it overwrites the first record. It doesn't go to the end of the record and write there....make any sense?


Option Explicit
Dim strSave As String

Private Sub cmdReport_Click()
'
' gintChildren = optChildren.Index
'
' Select Case gintChildren
' Case 0
' frmReport.lblChildren.Caption = "None"
' Case 1
' frmReport.lblChildren.Caption = "1 - 2"
' End Select
'
' frmReport.Show


End Sub

Private Sub mnuFileExit_Click()
Unload frmStatistics
End Sub

Private Sub mnuFilePrintForm_Click()
PrintForm
End Sub

Private Sub mnuFileSave_Click()
'Dim FF As Integer

gintChildren = gintChildren + 1
'gintChildren = adoUserStatistics.Recordset("User ID")
adoUserStatistics.Recordset("User ID") = gintChildren
'lblUserID.Caption = gintChildren

With adoUserStatistics.Recordset
.AddNew
End With

' FF = FreeFile()
'strSave = optGender(0).Name & Space$(2) & "Male" & Space$(5) & optGender(0).Value
'Open "a:\OptionSave.txt" For Output As #FF
' Print #FF, strSave
'Close #FF

End Sub

Private Sub optGender_Click(Index As Integer)

If optGender(Index).Value = 1 Then
strSave = strSave & "answer" & optGender(Index).Caption
End If

End Sub

Private Sub Form_Load()

With adoUserStatistics
.Refresh
'.Recordset.AddNew
gintUser = .Recordset.RecordCount
End With

gintUser = gintUser + 1
adoUserStatistics.Recordset("User ID") = gintUser

End Sub

***************************
Module:
Public gintChildren As Integer
Public gintUser As Integer

Thanks!

[Lethal]

Not sure if this will work, can't test it right now nor have I used the ADODC control in a while:
Add this to your save proc:

With adoUserStatistics.Recordset
    .AddNew
    .Recordset("User ID") = gintChildren
    .Update
End With

[Lethal]

You might just need to use the update method in the save proc or use a .moveprevious to refresh the recordset.

[maggieot]

should that be in the Form_Load or the Save procedure?

[Lethal]

If i can recall correctly, use would use the .AddNew to add clear all your textboxes for a new record. And when everything is entered you could just move the cursor (location in the recordset) or use the update method. Try putting it in the save procedure.

[Lethal]

Just tested it, that's what it is. Use the add New in your add event, and use update method when you are ready to save the new record.

[maggieot]

Can I see your code?

[Lethal]

This is just an example from another project from a while back.

Option Explicit

Private Sub cmdAdd_Click()
    adProducts.Recordset.AddNew
End Sub

Private Sub cmdDelete_Click()
    With adProducts.Recordset
        .Delete
        If Not .EOF Then
            .MoveNext
        Else
            .MoveLast
        End If
    End With
End Sub

Private Sub cmdProdSearch_Click()
    Dim sSearchText As String
    sSearchText = txtProdSearch.Text
    adProducts.Recordset.Find "ProductName Like 'G*'"
    ' "Productname >= '" & sSearchText & "'"
End Sub

Private Sub cmdUpdate_Click()
    adProducts.Recordset.Update
End Sub

[maggieot]

Is there a property that has to be set in the database that requires new data to be saved to the end rather than the beginning?

Right now everytime you leave the program and come back in, the primary number increments, but it saves to the first record.

[Lethal]

hmm....there is no such property. why dont u send me your project and I'll take a look. Incude the database.

[maggieot]

I'll get w/you later about this (if you have time).
check your e-mail
Thanks!

[Lethal]

No prob, well get it settled....