Inventory Save/Edit Functions

[Guest]

I'm working on a database to control the inventory of my companies assets. I
realize that Access automatically can create, edit, and automatically saves
information into a table when you link it directly and using the navigation
buttons. What I would like to do, however, is to create a form that I can
fill out with all the transactions of a specific order, and then to prevent
it from accidently being autosaved into the inventory, create a "Save"
button, or some kind of confirmation that transfers the data from the current
form to the Inventory. I've read bits and pieces here in the forum of
something about having unbound forms to do this, but I can't seem to find
anything that gives more specific directions on how to perform this
operation. Any assistance, or links to assistance would be greatly
appreciated. Thank you!

 

[tina]

using an unbound form for the data entry, i can think of a couple ways off
hand, to save the data into a table first, create your form. for ease of
reference, i would name each control to match the table fieldname that the
data will be assigned to (example: table - MyTable, field - FieldOne,
form - MyForm, control - txtControlOne). then you can use either of these
methods: 1) using DAO, open the table as a recordset and add a record,
setting the value of each field to the value in the corresponding form
control. 2) create an Append query that refers to the open form's controls
to append the data into the appropriate table fields. (if you're not
comfortable with VBA, this way would probably be easier for you to set up.)

to use solution 2, paste the following SQL statement into a new query (in
SQL view), changing the names of the table, fields, form, and form controls
to the correct names in your database. if your table has more or less than
four fields, adjust the statement using the same syntax. you can use a macro
on the form's command button to open the query (as an Action query, it will
"run", rather than opening a dataset).

INSERT INTO MyTable ( FieldOne, FieldTwo, FieldThree, FieldFour )
SELECT [Forms]![MyForm]![txtControlOne], [Forms]![MyForm]![txtControlTwo],
[Forms]![MyForm]![txtControlThree], [Forms]![MyForm]![txtControlFour]

to use solution 1, paste the following code into your form's module. change
the name of the command button's procedure (Command8) to the name of your
command button, and the names of the tables, fields and controls to the
correct names in your db. again, add or delete field/control references as
needed using the same syntax.

Private Sub Command8_Click()

If MsgBox("Are you sure you want to save this record?", _
vbDefaultButton2 + vbYesNo) = vbYes Then
AppendRecord
Else
MsgBox "The record was not saved."
End If

End Sub

Private Sub AppendRecord()

On Error GoTo AppendRecord_Err

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

Rst.AddNew
Rst("FieldOne") = Me!txtControlOne
Rst("FieldTwo") = Me!txtControlTwo
Rst("FieldThree") = Me!txtControlThree
Rst("FieldFour") = Me!txtControlFour
Rst.Update

MsgBox "Record saved to table."
EraseData

AppendRecord_End:
Rst.Close
Set Rst = Nothing
Exit Sub

AppendRecord_Err:
Select Case Err.Number
Case 3421
Me!txtControlOne.SetFocus
MsgBox "You entered an invalid value - letters instead of " _
& "numbers, or vice versa." & vbCR _
& "Correct your data and Save again, please."
Resume AppendRecord_End
Case Else
MsgBox Err.Number & " " & Err.Description, , Me.Name _
& ": AppendRecord_Err"
Resume AppendRecord_End
End Select

End Sub

Private Sub EraseData()

With Me
!txtControlOne = Null
!txtControlTwo = Null
!txtControlThree = Null
!txtControlFour = Null
End With

End Sub

hth