Can't Get Data from Form to Append to Table! HELP!!!!

[AvesRule33]

I have a form called frmWorkOrderEntry which has a number of text boxes to allow the information to be entered.

[UnitNo] (Combo box list of equipment)
Row Source = SELECT [SiouxCityCampusEquipment].[UnitNo], [SiouxCityCampusEquipment].[EquipType], [SiouxCityCampusEquipment].[Type], [SiouxCityCampusEquipment].[Make], [SiouxCityCampusEquipment].[ModelNo], [SiouxCityCampusEquipment].[SerialNo], [SiouxCityCampusEquipment].[Location] FROM SiouxCityCampusEquipment WHERE ((([SiouxCityCampusEquipment].[EquipType])="Truck")) ORDER BY [SiouxCityCampusEquipment].[UnitNo];

[EquipType] (Populated from UnitNo combo - control source =[UnitNo].column(1))

[Type](Populated from UnitNo combo - control source =[UnitNo].column(2))

And 5 other unbound fields that are populated from the UnitNo combo. User is then required to manually fill in
4 other unbound fields such as [WorkOrderNo], [ServiceDate], etc....

Once the user has selected the UnitNo from the combo list, the other unbound fields are populated, so that works fine.

The probem I am having is that once this information has been filled in, I want to use an AddRecord button to append the forms information into the REPAIRS table. So far I have not been able to make that happen?

Here is my Event Procedure that is located in the OnClick property of the AddRecord button that is giving me fits:

Private Sub cmdAddRecord_Click()

Dim strSQL As String
Dim db As Database

Set db = CurrentDb

strSQL = "INSERT INTO [Repairs] "
strSQL = [UnitNo] = Me.[UnitNo].Value
strSQL = [EquipType] = Me.[EquipType].Value
strSQL = [Type] = Me.[Type].Value
strSQL = [Make] = Me.[Make].Value
strSQL = [ModelNo] = Me.[ModelNo].Value
strSQL = [SerialNo] = Me.[SerialNo].Value
strSQL = [Location] = Me.[Location].Value
strSQL = [ServiceDate] = Me.[ServiceDate].Value
strSQL = [WorkOrderNo] = Me.[WorkOrderNo].Value
strSQL = [Hours] = Me.[Hours].Value
strSQL = [Description] = Me.[Description].Value
db.Execute strSQL


End Sub

The SerialNo field is the Primary Key in the SiouxCityCampusEquipment table. I do not have a Primary Key set in the Repairs table that I am trying to append the record to.

I also need to know how to clear the form afterupdate so that the user can select another UnitNo from the combo box and add another Work Order.

I hope I have provided enough information and have not confused the issue to much!! Any help would be "GREATLY APPRECIATED" as I have been batteling with this for days and the boss is breathing down my neck to get this database working

 

[AlexCuse]

This sounds more like an Access issue, but off the top of my head I think you should be using the RunSQL method to perform your append rather than the db.Execute.

Your strSQL query is also not even close to properly formatted.

Once you get your strSQL formatted as a proper update query, try

DoCmd.RunSQL strSQL

As far as resetting the form, that should be done automatically upon changing the combo box setting.

For better answers, try the Access Forums.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[AvesRule33]

Thanks for the response Alex. You are correct, I am doing this in Access (Thought that was the forum I was in?). At any rate, can you tell me what you mean by my strSQL is not formated right. I am fairly new to doing anything in VB but could not get it done using simple queries so I have been trying to piece this together with information and examples doing research on the web.

Any more specifics that you could provide would be Much Appreciated.

Thanks!

 

[AlexCuse]

To start, this code:

strSQL = "INSERT INTO [Repairs] "
    strSQL = [UnitNo] = Me.[UnitNo].Value
    strSQL = [EquipType] = Me.[EquipType].Value
    strSQL = [Type] = Me.[Type].Value
    strSQL = [Make] = Me.[Make].Value
    strSQL = [ModelNo] = Me.[ModelNo].Value
    strSQL = [SerialNo] = Me.[SerialNo].Value 
    strSQL = [Location] = Me.[Location].Value
    strSQL = [ServiceDate] = Me.[ServiceDate].Value
    strSQL = [WorkOrderNo] = Me.[WorkOrderNo].Value
    strSQL = [Hours] = Me.[Hours].Value
    strSQL = [Description] = Me.[Description].Value

Is simply changing the value of strSQL several times.

You want your strSQL to print like an actual query you would run, so it should be something like this:

strSQL = "insert into [repairs] values (""" & _
forms("FORMNAME").Controls("UnitNo").Value & ""","

This will give you the beginning of a properly formatted SQL statement, you just need to change FORMNAME and add the rest of the controls. And In place of the comma inside the quotes on the last control you will need a close parentheses --> )

Be careful with the quotes, that is what will get you stuck.

Hope this helps, and if you need more detail please try the Access Forums.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[AvesRule33]

THANK YOU Alex! I hade to play around a little but I got it to work and it works Beautifully!!

Thanks Again!

 

[AlexCuse]

Glad it worked for you. Next time check out the Access Modules forum, you will find people in there much smarter than I am (I'm in there on occasion too if you're unlucky)

Alex

A wise man once said
"The only thing normal about database guys is their tables".