AddNew not adding record to end of recordset?

[dmkolb]

Hello,
I am using a form in VB to access a db to view, add, edit, etc.
The addnew button adds a record to the bottom of the table but it also over writes the record the datacontrol happens to be on at the time the addnew is used. Below is some of the code, I hope someone can help me with this.

Option Explicit

Public db As Database
Public rs As Recordset
Public ws As Workspace
Public x As Integer
Public intMsg As Integer

Public Function initdb()
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("A:\inventory.mdb&quot
Set rs = db.OpenRecordset("table", dbOpenDynaset)
End Function

Private Sub cmdAddNew_Click()
x = 1
Set rs = db.OpenRecordset("table", dbOpenDynaset)
rs.MoveLast
txtPartNo.Text = ""
txtQuantity.Text = ""
txtPrice.Text = ""
txtDesc.Text = ""
lblID.Caption = rs.RecordCount + 1
txtPartNo.SetFocus
End Sub

Private Sub cmdDone_Click()
If x = "1" Then
rs.AddNew

rs("ID&quot = rs.RecordCount + 1
rs("PartNo&quot = txtPartNo.Text
rs("Quantity&quot = txtQuantity.Text
rs("Price&quot = Format(txtPrice.Text, "$##.##&quot
rs("Desc&quot = txtDesc.Text

rs.Update
intMsg = MsgBox("Record has been updated sucessfully!", vbOKOnly)
End If

If x = "2" Then
' Code here for edit function which seems to work ok
End If

Set rs = db.OpenRecordset("table", dbOpenDynaset)
rs.MoveFirst
End Sub

Private Sub Form_Load()

initdb

If rs.RecordCount > 0 Then
rs.MoveFirst
txtPartNo.Text = rs("PartNo&quot
txtQuantity.Text = rs("Quantity&quot
txtPrice.Text = Format(rs("Price&quot, "$##.##&quot
txtDesc.Text = rs("Desc&quot
lblID.Caption = rs("ID&quot
End If

End Sub

Another problem is when the form shows the price it isn't in the correct format?!
Any help would be appreciated, Thanks.

 

[zemp]

I think that your datacontrol is conflicting with your DAO code. You will probably be better to use one ot the other.

I have set up an ADO datacontrol (I just prefer it) to add and edit records from a table. Placed text boxes on the form and bound them to the ADO data control. The only code I needed was,

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

Private Sub cmdNext_Click()
Adodc1.Recordset.MoveNext
End Sub

Private Sub cmdPrevious_Click()
Adodc1.Recordset.MovePrevious
End Sub

Private Sub cmdSave_Click()
Adodc1.Recordset.MoveLast
End Sub

The ADO datacontrol took care of the rest. Just make sure its properties are set up correctly. .EOFaction set to asDoAddNew, etc.

BTW, the DAO code you posted worked fine when adding new records to unbound text boxes. It always added new records and did not overwrite.

The format function may give you a problem because it will always add a dollar sign even if one already exists and you are not filling spaces with zero's. Try,

txtPrice.Text = Format(rs("Price&quot, "#,##0.00&quot

Or you can format the database field.

Thanks and Good Luck!

zemp

 

[dmkolb]

Thanks for geting back to me Zemp. I am new to ADO. I set up an ADO control vs. DAO control and contacted the text boxes to it, but I'm getting am error now that I don't understand. 'Expected query name after EXECUTE' Can you tell me what I did wrong here?
Thanks dmkolb@comcast.net

 

[zemp]

dmkolb, we will need more details. Where did the error occur, how is your ADODC set up. What is your new code? For example what are following design time properties of the ADODC;

Commandtype: 2 - adCmdTable
Connectionstring: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=A:\Inventory.mdb;Persist Security Info=False
EOFAction: 2 - adDoAddNew
Recordsource: table

I have given an example of how I would set them after the colon ). Thanks and Good Luck!

zemp

 

[dmkolb]

Thanks for baring with me. Yes the settings you show are what I have. I started over with the VB Application Wizard and this is the basic of what I have now. Now I get an error in the Move_Complete section. Can you see what I'm missing? I also have no highlighted buttons on the control to use. Argghhh! Thanks again for this help.


Private Sub cmdCancel_Click()
With datPrimaryRS.Recordset
.MoveNext
If .EOF Then .MoveLast
End With
End Sub

Private Sub cmdDone_Click()
If x = 1 Then
datPrimaryRS.Recordset.AddNew
intmsg = MsgBox("Record has been added successfully!", vbOKOnly)
Exit Sub
ElseIf x = 2 Then
datPrimaryRS.Recordset.UpdateBatch adAffectAllChapters
intmsg = MsgBox("Record has been updated successfully!", vbOKOnly)
Exit Sub
End If
End Sub

Private Sub Form_Terminate()
intmsg = MsgBox("Are you sure you want to quit?", vbInformation)
Unload Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
End Sub

Private Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean)
MsgBox "Data error event hit err:" & Description
End Sub

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

'This will display the current record position for this recordset
datPrimaryRS.Caption = "Record: " & CStr(datPrimaryRS.Recordset.AbsolutePosition)
End Sub

Private Sub cmdAddNew_Click()
x = 1
txtPartNo.Text = ""
txtQuantity.Text = ""
txtPrice.Text = ""
txtDesc.Text = ""
txtPartNo.SetFocus

End Sub

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr
intmsg = MsgBox("Are you sure you want to delete this item?", vbOKCancel)

With datPrimaryRS.Recordset
.Delete
.MoveNext
If .EOF Then .MoveLast
End With
Exit Sub
DeleteErr:
MsgBox Err.Description
End Sub

Private Sub cmdUpdate_Click()
x = 2
intmsg = MsgBox("Change necessary data then click the Done button.", vbOKOnly)

End Sub

Private Sub mnuFileExit_Click()
intmsg = MsgBox("Are you sure you want to quit?", vbInformation)
Unload Me
End Sub
dmkolb@comcast.net

 

[zemp]

I still think that you are trying to do too much. Let the ADODC do the work. It will automatically clear the text boxes when you invoke the addnew method on the recordset. This should be done before you add information to the text boxes.

In a nut shell. First invoke the addnew method then add the information to the text boxes then invoke the update method.

If you want to update existing information just start typing. The ADODC will recognize that you are updating information. You can then call the update metod to ensure that it is saved. If you have updated information and move to another record the information is automatically saved.

All you really need in your buttons is below.

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

Private Sub cmdCancel_Click()
Adodc1.Recordset.CancelUpdate 'but doesn't redisplay old data
End Sub

Private Sub cmdNext_Click()
Adodc1.Recordset.MoveNext
End Sub

Private Sub cmdPrevious_Click()
Adodc1.Recordset.MovePrevious
End Sub

Private Sub cmdSave_Click()
Adodc1.Recordset.Update
End Sub

As far as the error goes. I never use ADODC events because I usually set the controls visible property to false. I have buttons to move through the recordset. Also most users will identify a record by the data it contains and not the absolute position.
Thanks and Good Luck!

zemp

 

[dmkolb]

Zemp you have been great in baring with me in this. I saw a note posted by VB5prgmr? that said to walk thru the data form wizard, which I did. BUT I still get the same Error in FROM statement with that! I know I've followed everything you and others have said including MSDN but I seem to have something else going on here in the background if the Wizard setup form gets the same Error?! Any way I can send you the whole project to look at the controls and all? If so what parts do you need to see? I would include the .vbp .frm and .mdb; Maybe I don't see something in one of them.
And thanks again! dmkolb@comcast.net

 

[dmkolb]

Zemp, thanks for all your help! I finally found the problem! It was in the database itself. I had called my table, (table) and I guess it confused everything. Once I changed the name of the table, everything connected. Thanks again. dmkolb@comcast.net