Input Box

[Guest]

In my syntax I can create input boxes for a number of fields but in one of
the fields which contains details of the number of stock on hand for that
item I am trying to work out the syntax to add stock for that item. I'll
provide you with the syntax I have tried so far.

Private Sub CmdAddStock_Click()

'this subroutines allows records to be added to a recordset

'declares a database object variable
Dim mydb As Database

'declares a recordset object variable
Dim myset As Recordset

'sets up a variable to continue adding records or not
Dim AnotherRecord As String

'sets the object variable mydb to the current database.
'currentdb() is a function that returns the current database.
Set mydb = CurrentDb()

'creates a copy of the recordset from the Item table in memory
Set myset = mydb.OpenRecordset("Item")

'loops until the user says they do not wish to add more records

Do
'adds a new record in the recordset
myset.AddNew

'gets the value of ItemDesc, Price and OnHand
myset!ItemDesc = InputBox("Enter the ItemDesc")
myset!Price = InputBox("Enter the Price")
myset!OnHand = InputBox("Enter the OnHand" + "Enter the amount to be
added")

'updates the recordset in the database
myset.Update

AnotherRecord = InputBox("Enter a New Record Y/N")

Loop Until AnotherRecord = "N"

'closes the recordset
myset.Close

End Sub

Any information will be appreciated.

 

[Douglas J. Steele]

What is your question?

Are you trying to increment OnHand by whatever value they input? If so, try:

myset!OnHand = myset!OnHand + InputBox("Enter the amount to be added")

 

[Guest]

The help you have provided has produced some progress in my procedure. This
is about the same syntax and I need to know how to select a record to edit a
field. I have made some changes to the syntax, which I would like to provide.

Private Sub CmdAddStock_Click()

'this subroutines allows records to be added to a recordset

'declares a database object variable
Dim mydb As Database

'declares a recordset object variable
Dim myset As Recordset

'sets up a variable to continue adding records or not
Dim AnotherRecord As String

'sets the object variable mydb to the current database.
'currentdb() is a function that returns the current database.
Set mydb = CurrentDb()

'creates a copy of the recordset from the Item table in memory
Set myset = mydb.OpenRecordset("Item")

'loops until the user says they do not wish to add more records

Do
'Edit a record in the recordset
myset.Edit

'gets the value of ItemDesc, Price and OnHand
myset!ItemId = myset!ItemId + InputBox("Select the ItemID")
myset!OnHand = myset!OnHand + InputBox("Enter the amount to be added")

'updates the recordset in the database
myset.Update

AnotherRecord = InputBox("Enter a New Record Y/N")

Loop Until AnotherRecord = "N"

'closes the recordset
myset.Close

End Sub

Any further information would be appreciated.

 

[Douglas J. Steele]

You certainly don't add the desired ItemId to an existing ItemId!

Prompt for the desired ItemId, then process it. The following assumes that
the ItemId is the primary key of the table:

lngDesiredId = InputBox("Select the ItemID")
myset.MoveFirst
myset.FindFirst lngDesiredId
If myset.NoMatch Then
' no record exists with that Id
Else
' put your code to update the record here
End If

If ItemId isn't the primary key, you can use the Seek method instead.

Note that I think that what you're doing is somewhat unusual. Why not
present them with a form and let them key the values into the grid, rather
than prompting them record by record in code?