Combo Box will not Record Selection to Table

[FabHead]

I've got a Maintenance Action form that the user enters data and it is stored in a table tblMaintenance. I have a System Combo Box and a Sub-System Combo Box, I wanted the Sub-System list based on a selection from the System list......Thanks to this Forum I was able to figure that problem out quickly, but it created another problem.

I fixed my Combo Box problem from reading the Microsoft knowledge base (Q209595).
I used the Categories example as System and Products as Sub-System.

Everything on my form works, however, the Sub-System is an unbound Combo Box that does not record the selection to the table tblMaintenance in the field SubSystemID. I tried entering SubSystemID in the ControlSource propery but only got an error. I'm sure VB code is the answer.
So I guess I would like some help in coding the Sub-System Combo to save the selection to the table after saving the record.
Thanks in advance for any help.

 

[FoxProProgrammer]

Hi,

All you have to do is create a record set to save the fields. Do the following:

Dim Db as Database
Dim recset As Recordset

Set Db = CurrentDb
Set recset = Db.OpenRecordset("tblMaintenance" , dbOpenDynaset)

recset!<fieldname1> = <value>
recset!<fieldname2> = <value>
recset!........

recset.Update

recset.Close
Db.Close

If the combo box is named cbobox, and has two columns, you can save each column as follows:

recset!<fieldname1> = cbobox.Column(0).Value
recset!<fieldname2> = cbobox.Column(1).Value

The <> around fieldname1 and fieldname2 are not part of the syntax. I included them to show where you type the field name from your table. Incidentally, Db and recset are user defined names. You can name them anything you like.

dz

 

[FoxProProgrammer]

By the way, you would put this code in an event procedure that runs when you save the record...such as the OnClick event of a command button.

 

[FabHead]

Thanks for the help, should work....but I need to define Database. I put the code in my SaveClose command. As I am only having problems with this one field, that is the only one I coded for saving.
I got a Compiler Error:User Defined Type not Defined.
Here is the code.

Private Sub SaveCloseMaintenance_Click()
On Error GoTo Err_SaveCloseMaintenance_Click

Dim Db As Database CREATES ERROR
Dim recset As Recordset

Set Db = CurrentDb
Set recset = Db.OpenRecordset(&quot;Maintenance&quot;, dbOpenDynaset)

recset!SubSystemID = Subsystem.Column(0).Value
recset.Update
recset.Close
Db.Close

DoCmd.Close

Exit_SaveCloseMaintenance_Click:
Exit Sub

Err_SaveCloseMaintenance_Click:
MsgBox Err.Description
Resume Exit_SaveCloseMaintenance_Click

End Sub

Thanks Again!

 

[FoxProProgrammer]

Hi Fab,

If you haven't ever used the Database Type, you probably need to include a reference to the library that contains the definition. To do this, open the VB editor from within Access. Select Tools, References, and scroll down until you get to the library named Microsoft DAO 3.6 Object Library. Check the box, click Ok, and your code should work. You can also explicity define the database and record set as DAO types by using the following code:

Dim Db as DAO.Database
Dim recset as DAO.Recordset

dz

 

[FabHead]

Just want to say Thanks for the quick responses you have given me. I added the DAO 3.6 Object library, but now get a type mismatch RunTime error 13 at the following line:
Set recset = Db.OpenRecordset(&quot;tblMaintenance&quot;, dbOpenDynaset)

Everything seems ok, but I will give some details.

tblSubSystem has three fields:
SubSystem ID AutoNumber
SubSystemName Text
SystemID Number (tie SubSystem with a System)

tblMaintenance has a field called SubSystemID with a SQL in the RowSource offrom Lookup Wizard)
SELECT tblSubSystem.SubSystemID, tblSubSystem.SubSystemName, tblSubSystem.SystemID
FROM tblSubSystem;

Not sure what other information would help, kind of a rookie at this Access stuff. But I do appreciate your help.

Thanks

 

[FoxProProgrammer]

Fab,

You wrote: &quot;tblMaintenance has a field called SubSystemID with a SQL in the RowSource of&quot;

I'm a little confused by that. Is tblMaintenance the name of a table or the name of a form? It sounds like your table is named &quot;tblSubSystem&quot;. If so, set the record set as follows:

Set recset = Db.OpenRecordset(&quot;tblSubsystem&quot;, dbOpenDynaset)

There might be an easier way to do this. If you used the wizard to create the save button, it probably created the following code:

DoCmd.RunCommand acCmdSaveRecord

If so, you should be able to assign the value of the tblMaintenance.SubSystemID as follows:

SubSystemID = subsystem.Value

In this example, subsystem.Value is the value of a combo box named subsystem, and SubSystemID is the name of the field in your table. Put this line just before the DoCmd.RunCommand line.

I hope that you don't think I'm sending you on a wild goose chase, but I think that I understand your issue better now. You can do it the other way as well, but this way is a little easier.

By the way, there's an error in the syntax of:

recset!SubSystemID = Subsystem.Column(0).Value

You should use either the column property or the value property, but not both together. Depending on what you have the BoundProperty value set to, the value of the control would be equal to the data in a particular column. For example, if you have the BoundProperty set to 1, control.column(1) and control.Value will contain the same data.

 

[FabHead]

FoxPro,
I have not done a very good job of explaining what I have, I guess it's hard to see what someone else is doing.
I have a form called Maintenance and it's RecordSource is tblMaintenance (where all the selections are stored).

tblMaintenance has:
Field Name Data Type
Maintenance ID AutoNumber
ToolID Number (Lookup Wizard)
TypeMaintenanceID Number (Lookup Wizard)
EmployeeID Number (Lookup Wizard)
StartDate Date/Time
StartTime Date/TIme
FinishDate Date/Time
FinishTime Date/Time
TotalDownTime Number
SystemID Number (Lookup Wizard)
SubSystemID Number (Lookup Wizard)
SymptomID Number (Lookup Wizard)
Problem Memo
CorrectiveAction Memo
Open Yes/No
Failure Yes/No

Now all the fields in this table that end in xxxxxxID have a table. In the form Maintenance these are all ComboBoxes to allow the user to select from a list.

tblSystem has:
SystemID AutoNumber
SystemName Text

Also tblSystem has a SubDataSheet of SubSystem to tie the two together.

Now the System comboBox has the following properties:
Name ---------------> System
Control Source -----> SystemID
Row Source Type ----> Table/Query
Row Source ---------> tblSystem
Bound Column -------> 1

Also to get the proper Subsystem to display for a given System selection the following code in in the After Update of the System ComboBox:
Private Sub System_AfterUpdate()
Me.Subsystem.RowSource = &quot;SELECT SubSystemName FROM&quot; & _
&quot; tblSubSystem WHERE SystemID = &quot; & Me.System & _
&quot; ORDER by SubSystemName&quot;
Me.Subsystem = Me.Subsystem.ItemData(0)
End Sub

tblSubSystem has:
SubSystemID AutoNumber
SubSystemName Text
SystemID Number (lookup Wizard)


The SubSystem Combobox has the following properties:
Name ---------------> SubSystem
Control Source -----> Unbound
Row Source Type ----> Table/Query
Row Source ---------> Dependant on the code listed above
Bound Column -------> 1

And this is the code I have under a command labeled &quot;Save and Close on the Maintenance form:

Private Sub SaveCloseMaintenance_Click
On Error GoTo Err_SaveCloseMaintenance_Click

Dim Db As DAO.Database 'DAO cleared up Type Mismatch
Dim recset As DAO.Recordset 'DAO cleared up Type Mismatch

Set Db = CurrentDb
Set recset = Db.OpenRecordset(&quot;tblMaintenance&quot;, dbOpenDynaset)
recset.Edit 'This cleared 3020 error
recset!SubSystemID = Subsystem.Column(1)

recset.Update
recset.Close
Db.Close

DoCmd.Close acForm, &quot;Maintenance&quot;, acSaveYes

Exit_SaveCloseMaintenance_Click:
Exit Sub

Err_SaveCloseMaintenance_Click:
MsgBox Err.Description
Resume Exit_SaveCloseMaintenance_Click

End Sub

So, know when I click the &quot;Save and Close&quot; button the above code executes, everything is saved in tblMaintenance but....you guessed it, the SubSystem. That field is blank.
I know that's a lot, I would be happy to email you a copy of my database if that would help...it's about 4Mb.

This one little (turning out to be big) is just kicking my butt right now, so I am gratefull for any and all help.

Thanks again
Hoped this was a more helpful post.

 

[FoxProProgrammer]

Thanks, Fab. That helps a lot. Hopefully after all the work you went through to post the details, we can figure this out. I may have found the problem, but I'm not sure.

The Query for the subsystem combo box only has one field: subsytem name. However, you set the bound column to 1 and the value of subsystemID to the value of the item in column 1. Since you are only selecting one column, and Access starts counting with 0, column 1 would be empty and you'd be storing an empty value. You can verify this by running the debugger, setting a breakpoint before the command below executes, and holding your mouse over the expression on the right side of the = sign. Alternatively you can drag that expression into the watch list and see what its value is. I suspect that it's empty.

recset!SubSystemID = Subsystem.Column(1)

If this is true, you should be able to correct this situation by replacing

recset!SubSystemID = Subsystem.Column(1)

with

recset!SubSystemID = Subsystem.Column(0)

or

recset!SubSystemID = Subsystem.Value

Also, change the bound column of the subsystem combo box to 0.

I hope this corrects the problem. If not, I'd be happy to look at your database. Do you have WinZip? I have found that you get good compression with Access databases. You might also click on Tools, Database utilities on the Access menu and select Compact and repair database. If you haven't done it in a while you might be surprised how much the size of your database will shrink.

Let me know how it goes.

dz

 

[FabHead]

dz,
I tried what you suggested and I believe you are in the right area, but I must have another problem. Before changing anything I checked and had a null value on both sides of the expression:

recset!SubSystemID = Subsystem.Column(1)

I then changed this to:

recset!SubSystemID = Subsystem.Column(0)

and the right side was equal to the SubSystem selected in the combobox....I'm thinking then I might have it but it also gave me a Data Type Conversion Error.
When I changed the bound column in the SubSystem ComboBox then I lost the list for the Subsystem on the Combo Box (that's not good).

I did Compact and Repair and the size is down to 169K after zip.

I need your email address.
I'm sure it will be much easier to see the problem with the database.
Thanks Again
RV