Visual Basic&database, Newbie Update field in DB using AutoNumber field

Magi
#1 / 3

Newbie Update field in DB using AutoNumber field

The record is in a database.
The database is displayed in a DBGrid.
I click on a record in the DBGrid.
That selects the record I want to update.
I then click on a Command _Button.
Each time I click on the button I want
to update the QTY field in this record by the
value of wsQty.

I have a AutoNumber field in my database named "RecID".
When I click on a record in a DBGrid, I want the value
of wsQty to be placed in the "QTY" field of THAT record.

The following code places the value of wsQty in the
"QTY" field of the last record only.

Private Sub Qty_UpDate()

If Data1.Recordset.EOF Or Data1.Recordset.BOF Then
Exit Sub
End If

Data1.Recordset.MoveFirst
Do While Data1.Recordset.Fields("RecID") <> wsRecID
Data1.Recordset.MoveNext
Loop

Data1.Recordset.Edit

If wsQty > 1 Then
Data1.Recordset.Fields("Qty") = Str(wsQty)
Else
Data1.Recordset.Fields("Qty") = ""
End If

Data1.Recordset.Update
Data1.Refresh

End Sub

Sun, 04 Jul 2004 06:20:01 GMT

Stephen Mussle
#2 / 3

Newbie Update field in DB using AutoNumber field

You could simply send a SQL update string via the Connection object.
If using ADO, it would be something like the following:
strSQLcmd = "Update YourTableName set QTY=" & wsQTY & " where RecID="
& wsRecID
adoConn.Execute (strSQLcmd)
--

Quote:

> The record is in a database.
> The database is displayed in a DBGrid.
> I click on a record in the DBGrid.
> That selects the record I want to update.
> I then click on a Command _Button.
> Each time I click on the button I want
> to update the QTY field in this record by the
> value of wsQty.

> I have a AutoNumber field in my database named "RecID".
> When I click on a record in a DBGrid, I want the value
> of wsQty to be placed in the "QTY" field of THAT record.

> The following code places the value of wsQty in the
> "QTY" field of the last record only.

> Private Sub Qty_UpDate()

> If Data1.Recordset.EOF Or Data1.Recordset.BOF Then
> Exit Sub
> End If

> Data1.Recordset.MoveFirst
> Do While Data1.Recordset.Fields("RecID") <> wsRecID
> Data1.Recordset.MoveNext
> Loop

> Data1.Recordset.Edit

> If wsQty > 1 Then
> Data1.Recordset.Fields("Qty") = Str(wsQty)
> Else
> Data1.Recordset.Fields("Qty") = ""
> End If

> Data1.Recordset.Update
> Data1.Refresh

> End Sub

Sun, 04 Jul 2004 06:42:55 GMT

Magi
#3 / 3

Newbie Update field in DB using AutoNumber field

But I'm using DAO! What would the SQL look like then?

Magic

Quote:

> You could simply send a SQL update string via the Connection object.
> If using ADO, it would be something like the following:
> strSQLcmd = "Update YourTableName set QTY=" & wsQTY & " where
RecID="
> & wsRecID
> adoConn.Execute (strSQLcmd)
> --

> > The record is in a database.
> > The database is displayed in a DBGrid.
> > I click on a record in the DBGrid.
> > That selects the record I want to update.
> > I then click on a Command _Button.
> > Each time I click on the button I want
> > to update the QTY field in this record by the
> > value of wsQty.

> > I have a AutoNumber field in my database named "RecID".
> > When I click on a record in a DBGrid, I want the value
> > of wsQty to be placed in the "QTY" field of THAT record.

> > The following code places the value of wsQty in the
> > "QTY" field of the last record only.

> > Private Sub Qty_UpDate()

> > If Data1.Recordset.EOF Or Data1.Recordset.BOF Then
> > Exit Sub
> > End If

> > Data1.Recordset.MoveFirst
> > Do While Data1.Recordset.Fields("RecID") <> wsRecID
> > Data1.Recordset.MoveNext
> > Loop

> > Data1.Recordset.Edit

> > If wsQty > 1 Then
> > Data1.Recordset.Fields("Qty") = Str(wsQty)
> > Else
> > Data1.Recordset.Fields("Qty") = ""
> > End If

> > Data1.Recordset.Update
> > Data1.Refresh

> > End Sub

Sun, 04 Jul 2004 20:12:17 GMT