MS Access - Combox box and forms

  Sir Radfordin 11:23 24 Jul 03

Working on an Access Database and can't work out how to do this:

2 tables:

Table 1 (T1) is called tl_subsorderdetails and has the following fields

subsorderID, subs_type_id, Purchaseordernumber, orderdate, unitprice, discount, subsyear and OrganisationID.

Table 2 (T2) is called tl_subscription_type and has the following fields:

subs_type_id, subs_type_code, subs_type_description and subs_cost.

There is a relationship between T1 and T2 on subs_type_id.

Have a form that based on T1 which has a combo for subs_type_id. When you choose a row from this I want the subs_cost to be filled in based on the information in T2.

Sorry its a long post, my brain has died on me!

  IanNiblock 12:09 24 Jul 03

I'm not sure exactly what you are trying to do here:

Do you have another control on the form that you want to populate with subs_cost or do you want the combo box itself to show that value?

Also is this form used for adding data or simply displaying it?


  Sir Radfordin 12:12 24 Jul 03

The form is going to be used for adding data.

There is a combo box in T1 on subs_type_id which is a look up of Subs_type_id on T2.

When you select an option from the combo box I want to form to populate unitprice on T1 with subs_cost from T2 (where T1 subs_type_id = T2 subs_type_id)

Feeling very brain dead!

  The Transporter 12:15 24 Jul 03

You will need to do the following.

Create an unbound form, by double clicking on 'Create form in design view'

Now add the combo box(es) you need to set the parameters of the query.
Just click on the combo box button on the toolbar, the position your cursor
on the form, where you wish the combo box to be situated, then click and drag
to the size you want.

Follow the wizard, selecting the following.

'I want the combo box to look up the values in a table or a query.' > Next

Select the table which has the field you wish the combo box to have. > Next

Select the field you want by double clicking it. > Next and Next again

Give the combo box a label, this will be the label which will appear on your form.
> Finish.


The next step is to add a command button to the form which will run the query.

To do this:

Just click on the command button on the toolbar, the position your cursor on the form, where you wish
the command button to be situated, then click and drag to the size you want.

Follow the wizard, selecting the following.

Categories - select 'miscellaneous' Actions -select 'Run Query' > Next

Highlight the query, you wish to run > Next

Give the command button either a picture or text. (You can change this later, if you change your mind.) > Next

Give the command button a name > Finish

Close, Name and save the form.


Now set the following properties for the form.

To open the properties section of the form, in design view, right click on the upper left hand corner of the form,
where the two rulers meet a black square will appear along with a drop down list. Select properties.

Select the 'Other' tab. In the Popup property box, click Yes. In the Modal property box, click Yes.

Select the 'Format' tab.

In the BorderStyle property box, select Dialog. If you select the Dialog setting, the form has a thick border and
can include only a title bar and a Control menu. The form can't be maximized, minimized, or resized.


Now add the parameter(s) to the query.

Open the query in design view.

In the parameter row under the field you wish to set the parameter for, right click in this section and select 'Build'

In the three columns (boxes) at the bottom of the Expression builder in the first column, select the unbound form
you have just made, by highlighting it. In the second column select the name of the combo box you made on the form,
which corresponds to this parameter. Double click this, this will place it in the top of the window area. Press OK.

The parameter should now appear in the area selected.

Close and save the query.


Open the form, make a selection from the combo box and press the command button to see if everything is working.

  IanNiblock 12:29 24 Jul 03

This may be a long way of doing things, and somebody else may be able to suggest a much more efficient way - but it should work:

In the AfterUpdate event of your combo box use the following code:

Dim oDb as Database
Dim oRs as Recordset
Dim strSQl as String

Set oDb = CurrentDb

strSQL = "SELECT subs_cost FROM T2 WHERE subs_type_id = " & Me.ComboBox.Value

(Replacing ComboBox with the name of the combo box on your form)

SET oRs = oDb.OpenRecordset(strSQL)

You should then be able to say something like:

Me.TextBox1 = oRs(0)

To set the value in a textbox on your form to the required value.

Set oRs = Nothing
Set oDb = Nothing

If this doesn't do what you want then post back and I will endeavour to help further.


  Sir Radfordin 13:09 24 Jul 03

Ian, thanks for that. As it was the shorter solution it was the one I tried first.

Only got one problem, the Dim oDb as Database Dim oRs as Recordset Dim strSQl as String and oRs.Close Set oRs = Nothing Set oDb = Nothing don't seem to be correct.

If you comment these bits out then it doesn't seem to change anything (though it might if I understood what those bits did!).

Dim oDB as Database doesn't seem to be correct when VBA prompts with the next bit from a drop down menu.

  IanNiblock 13:41 24 Jul 03

In the VBA editor go to Tools->References and let me know (in the order they appear in the list) what items are ticked.

  Sir Radfordin 13:52 24 Jul 03

Top four are ticked which are:

VB for Applications

Microsoft Access 9.0 Object Library

OLD Automation

Microsoft ActiveX Data Objects 2.1 Library

  IanNiblock 13:58 24 Jul 03

I have the following in my list (after VBA and Micorosft Access Object Library - which cannot be moved or removed):

Microsoft DAO 3.51 Object library

Microsoft ActiveX Data Objects 2.7

If you can find the DAO library on your machine and place a tick in the box next to it, then move it up the list until it is above the ActiveX Data Objects item (Using the arrow buttons on the right hand side) I think the code should work?

  Sir Radfordin 14:16 24 Jul 03

Adding in Microsoft DAO 3.51 Object library seemed to help with the first bits but then the SET oRs = oDb.OpenRecordset(strSQL) stopped working.

If I don't include the Dim oDb As Database Dim oRs As Recordset Dim StrSQL As String or oRs.Close Set oRs = Nothing Set oDb = Nothing does anything not happen?

The testing I've done indicates that its fine?

  IanNiblock 14:24 24 Jul 03

What was the error message that you received when the oDb.OpenRecordset stopped working?

I have found with access that if something is working but you cannot explain why then it is best not to ask any questions!!

Can you post the complete Procedure (From Sub ComboBox_AfterUpdate() to End Sub) - that may give me some clues?



This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac