Command button blues

[cranebill]

Ok here is the situation, I was tring to build a form to input several items into a single new record. The form was very long and tedious so i broke it up into several forms. Now I have all these nice little forms but they each start a new record which defeats the purpose. So i looked in an existing database we have here and was looking to see how someone else did it. This is the code i got from there button on the form:

Private Sub Button6_Click()
On Error GoTo Err_Button6_Click

Dim DocName As String
Dim LinkCriteria As String
Dim Order As Recordset
Dim DB As Database

Set DB = DBEngine.Workspaces(0).Databases(0)
Set Order = DB.OpenRecordset("Main_Order_Table", DB_OPEN_DYNASET, DB_APPENDONLY)

Order.AddNew

Order!CustomerName = Field18
Order!CustomerAddress = Field8
Order!CustomerCity = Field10
Order!CustomerZip = Field14

Order.Update

DocName = "Sales_Rep_Choice"

DoCmd.Close
DoCmd.OpenForm DocName, , , LinkCriteria

Exit_Button6_Click:
Exit Sub

Err_Button6_Click:
MsgBox Error$
Resume Exit_Button6_Click

End Sub


looks simple doesnt it... i thought that as well so i modified it to meet my needs and here it is as follows:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim DocName As String
Dim LinkCriteria As String
Dim Order As Recordset
Dim DB As Database

Set DB = DBEngine.Workspaces(0).Databases(0)
Set Order = DB.OpenRecordset("Master Delivery Schedule", DB_OPEN_DYNASET, DB_APPENDONLY)

Order.AddNew

Order![Job Type] = Jobtype

Order.Update

DocName = "New Job Customer"

DoCmd.Close
DoCmd.OpenForm DocName, , , LinkCriteria

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Error$
Resume Exit_Command2_Click

End Sub

It throws me an error which says Compile Error: User-defined type not defined then it highlights Dim Db as Database in Blue then when i click ok it has an arrow and highlights "Private Sub Button6_Click()" in Yellow....... i have no hair left. Please help lol

 

[cranebill]

oops i mean the error is in the line command2 not button6

 

[virtualjm]

Access does not understand the "database" type - therefore thinks it to be user-defined. My guess is that you are using Access 2000 or later, and that ADO is the default library. To use the Database type you need to include a reference to the DAO library. Do this from the VB editor by going to "Tools" then "References". Search down the list for "Microsoft DAO 3.51 Reference Library". Put on a check to include this reference. You will know if you have been successful when you start typing "Dim db AS ", and the autohelp should find "Database" in the list as you begin typing it. Good luck.

 

[cranebill]

Well that sorta worked, There were two different DAO's so i tried them both and both times i get another error: "Type Mismatch" im not sure what that means so i then checked the spelling of all my mods to the code and they are correct. Any additional Ideas would be greatly appreciated

 

[cranebill]

Order![Job Type] = Jobtype

well i did a little debug and i know it the problem is in this line but cannot figure it out. How can it be a type mismatch?

 

[virtualjm]

Check the data type of the Orders.[Job Type] in the table and the Jobtype variable, which I assume you are getting from the form itself. Do they match? Where exactly is Jobtype coming from? Is it a text box field on the form (so do you need Jobtype.value?), or otherwise how is Jobtype being assigned its value? Just a guess. Good luck.

 

[cranebill]

I just figured it out through a little research so i figured i would share, Thanx Virtual for you put me on the right track:

I had to do this this is an example i got from the net.

Dim rstProgram As DAO.Recordset
Dim dbCurrent As DAO.Database

now it works, thanx again