Solved: Add Unbound Record to Table Using Event Procedure

[feathers212]

I am very new to modules within MS Access, so please be patient! I am using Access 97.

I have a form that I want to use to create records of material receipts. There are three input boxes:


ReceiptDate ? a textbox with current date as default
ReceiptMaterial ? a combobox that retrieves its dropdown values from a master materials sheet
Quantity ? a textboxNone of the inputs are bounded to any tables, etc. The user input all values and then proceeds to click the ?Submit Report? button. When the button is clicked, the transaction is supposed to be fed into the ?Material Transactions? table as a new record.

The button has the following Event Procedure:


Private Sub Submit_Receipt_Click(ReceiptMaterial As String, ReceiptDate As Date, ReceiptQuantity As Integer)
On Error GoTo Err_Submit_Receipt_Click

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Material Transactions")

With rs
.AddNew
.Fields("Material Name") = ReceiptMaterial
.Fields("Date") = ReceiptDate
.Fields("Transaction") = "Receipt"
.Fields("Quantity") = ReceiptQuantity
.Update
.Close
End With

Exit_Submit_Receipt_Click:
Exit Sub

Err_Submit_Receipt_Click:
MsgBox Err.Description
Resume Exit_Submit_Receipt_Click

End Sub



When I hit the button, I get the following error message:

The expression On Click you entered as the even property setting produced the following error: Event procedure declaration does not match description of even having the same name.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

I?m not sure what I am doing wrong. Does it have something to do with how the variables are fed into the coding? Like I said, I am new to all of this and am unsure of what I need to do. I need to do something similar with several other tables, but am unsure about how to go around doing it!

Thanks for any input and help!!
~Heather

[JimmyTheHand]

Hi, Heather

I don't think these parameters
(ReceiptMaterial As String, ReceiptDate As Date, ReceiptQuantity As Integer)
are needed here. Try deleting them.

If it's not enough, copy the code to a textfile for backup purpose, delete the event procedure, create another one from the properties list of the button, so that it is correctly registered. Finally put the code back into the new procedure.

Jimmy

[feathers212]

I don't think these parameters
(ReceiptMaterial As String, ReceiptDate As Date, ReceiptQuantity As Integer)
are needed here. Try deleting them.

I deleted the parameters. When attempting to run the code, I got a "Data type conversion error" message.

I have also rebuilt the button and pasted my code, but am still getting the error message previously posted.

I have even attempted to reference the information from the form, but it also gives me a "Data type conversion error":
.Fields("Material Name") = [Forms]![Receipt Form]!ReceiptMaterial

Please help! This is one of the biggest steps in my project, and once I get it figured out, everything else should flow smoothly!

Thanks!
~Heather

[JimmyTheHand]

Hi

I geuss you get this error because you want to write a textbox object into a string type field. I suggest to try this code, I always use this way of referencing, with success.


Private Sub Submit_Receipt_Click()
On Error Goto Err_Submit_Receipt_Click

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Material Transactions")

With rs
.AddNew
![Material Name] = Me.ReceiptMaterial.Value
!Date = Me.ReceiptDate.Value
!Transaction = "Receipt"
!Quantity = Me.ReceiptQuantity.Value
.Update
.Close
End With

Exit_Submit_Receipt_Click:
Exit Sub

Err_Submit_Receipt_Click:
MsgBox Err.Description
Resume Exit_Submit_Receipt_Click

End Sub I think the most important thing is the .Value at the end of those 3 lines.

EDIT
Also, I'd switch off the error handler part, for debug purpose, so that the run breaks at the error, and the wrong part of code is highlighted.

[feathers212]

I think the most important thing is the .Value at the end of those 3 lines.

I had a feeling that I was going to need something like this (I've used it many times with Excel). I had tried using it, but it didn't work with the way I had my code. I realize now that it was the way I was trying to feed to the table: ![Material Name] was key!

Thanks for your help Jimmy!! :thumb If I have any more problems, I'll be sure to let you know!

~Heather

[JimmyTheHand]

If I have any more problems, I'll be sure to let you know!
~Heather

Sure :)
Glad it worked out.

Jimmy