Help with Compile error

[Gregm66]

Hi all,

I have created a form to save my recipes to my table Called Table2.
Every time I click on the save button I get the following error.

User-Defined type Not Defined.

I am filling out all my text boxes and trying to save them to my table.



Any help would be appreciated, I'm not quite sure where I have gone wrong.

Below is the code I am using,

Option Compare Database
Option Explicit



Private Sub CmdSave_Click()
    'When a User is finished adding a Recipe, and presses Save.
If Me.txtName = "" Or Me.txtIngredients = "" Or Me.txtMethod = "" Or Me.txtNotes = "" Or Me.txtNutritional = "" Then
    MsgBox "Please make sure make sure all fields are filled in place a full stop in a field if no info required", vbInformation
    Exit Sub
End If
Dim cmd As Command
Set cmd = New Command
'Query that inserts a new Recipe into the Recipe Table. Users will not be able to access the DB if they are not in the User Table.
    cmd.ActiveConnection = CurrentProject.Connection
        cmd.CommandText = "INSERT INTO Table2 (Category, RcpName, rcpIngredients, rcpMethod, Nutritional, PerServe, rcpNotes, Image) " _
                        & "VALUES('" & Me.txtCategory & "', '" & Me.txtName & "', '" & Me.txtIngredients & "', '" & Me.txtMethod & "', '" & Me.txtNotes & "', '" & Me.txtNutritional & "','" & Me.txtCount & "', '" & Me.txtImageName & "')"
        cmd.Execute
Set cmd = Nothing
Me.Refresh
End Sub

[Micron]

You are likely missing a project reference - probably ADO.
It helps if you specify which code line the failure occurs on. When you Dim something that belongs to ADOB or DAO, you should always be explicit about the library, such as
Dim cmd As ADODB Command

[Gregm66]

thanks for your reply Micron
the first line highlighted yellow is:

Private Sub CmdSave_Click()

then highlighted blue is:

Dim cmd As Command

[Micron]

Not meaning to be condescending, but did you understand my proposed solution?

[Gregm66]

to be totally honest with you Micron, I am not quite sure what you meant, I am only new to programming in access, so excuse my ignorance when I say I have no idea, I am still trying to get my head around the coding side of things.

[Micron]

OK, you must know how to get at the code since you've either created it or copied and pasted it into a button click event. This is done in the VB editor, where you must have a reference to any "library" that Access needs, or else you get the message you got. Some of these references cannot be removed (un-referenced), but many are optional. Access thinks that the object you're trying to create is a user-created thing because you've not given Access any reference needed for it to create and manage the object.

In the vb editor you should find the Tools > References menu item. Select that and a dialog showing lots of available references from which you need to locate and check the box for (I believe) an ActiveX Data Objects library version. Which one I can't tell you since I rarely use ADO, but I'd try the version with the highest number.

[Gregm66]

thanks Micron

I have done as you suggested, but still not working, would this reference still apply if I changed my code to the Following:
I am trying different approaches from code that I have found by searching google etc. but I am still having difficulties getting it to save the form data back to the table.

Option Compare Database
Option Explicit



Private Sub CmdSave_Click()
    Dim db As Database
Dim rec As Recordset


Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from Table2")


rec.AddNew
rec("Category") = Me.txtCategory
rec("RcpName") = Me.txtName
rec("rcpIngredients") = Me.txtIngredients
rec("rcpMethod") = Me.txtMethod
rec("Nutritional") = Me.txtNutritional
rec("PerServe") = Me.txtCount
rec("rcpNotes") = Me.txtNotes
rec("Image") = Me.txtImageName
rec.Update


Set rec = Nothing
Set db = Nothing
End Sub

[Micron]

would this reference still apply if I changed my code to the Following

The reference mentioned may not apply but the problem may persist for the same reason. I stated that you should always be explicit about the library yet you still are not. Wherever you got that code from, it is not written as it should be. When working with record sets, one typically declares them as ADO or DAO record sets, depending on which reference you have set. If you don't, Access will pick the first library reference in order of your set preference, but if you have not referenced either library, expect the problem still.

What you're doing seems overly complicated for the stated objective. It should be a simple matter to either navigate to a new record in the form's record set and add the info or open the form to a new single to add the info. This can be affected by your design approach; e.g. is it a main/subform design, or a single form with separate controls, or a datasheet design, etc. Maybe take a look at this or Google "ms access add record to table from form".

Re: I have done as you suggested, but still not working,
- as my signature says, "doesn't work doesn't help".

[Gregm66]

ok I understand what you are saying Micron, my Recipe form has a filter and 2 cascading combo boxes, every time I go to add new record and try to enter into the Recipe name combo I get an error (Debug), due to the fact that I have also a field that references an image to for my for that is where it stops. hence why I wanted to make just a new recipe entry form to try and avoid this problem.
the problem that keeps appearing this way is as follows.

Private Sub Combo9_Change()
    Me.Pic1.Picture = Me.Combo9.Column(2)
End Sub

[Micron]

How many columns in the combo box? You realize that the list box and combo box column property is a zero based property (i.e. 0 is the first column, 1 is the 2nd and so on...)?
You are beginning to lose me as to what's going on. It's like you had something that wasn't working (you're still being vague about the failure - "that is where it stops.") so you started over and that's where this post began. Now it seems like we're talking about what you originally had. At this point, I think it would be best if you posted a zipped copy of your db and gave explicit details as to what you're after and what's happening when you try to achieve whatever it is you're trying to achieve. We're at 9 posts now and seem to be going nowhere fast...