473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

use one combobox to fill another in a subform...

64 64KB
Hi there,

I have a problem with a subform on a mainform.
I have;

Databases amongst which;

tbl_trajecten (including field: ID_traject)
tbl_reasons (including field: ID_reason_traject)

a main form called : frm_traject_detail
a form called : frm_reasons (which is used as a subform on the main form using ID_traject as linking master field and ID_reason_traject as linking child field - this is a continous form because the user can put in multiple reasons)

On the main form i let users put in data of prospects.
On the subform i want users to indicate why a prospect (salesopportunity) is won or lost (can be multiple reasons).

On the subform i have 2 comboboxes;

[cmb_reason_type]
[cmb_reason]

The record source of subform frm_reasons is a table called tbl_reasons

The record source of [cmb_reason_type] is; a query that lists one field from a query from tbl_reasons called [reason_type]
this is an unbound combobox.

The record source of [cmb_reason] is all fields from tbl_reasons
Record source is [Reason] from the tbl_reason (which is a numeric field)
Bound column is 1: [ID_reason] (not visible, because user will see column2 in which the name of the reason is listed.

When I choose an item from [cmb_reasons_type], an afterupdate event makes sure that [cmb_reasons] will list only reasons of a certain type (as indicated in the [cmb_reasons_type] combobox).

In the after_update event of the first combobox [cmb_reason_type] i have the following code;

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmb_reason_type_AfterUpdate()
  2. Dim strreden As String
  3. strreden = "SELECT * FROM Q_reason "
  4. strreden = strreden & "WHERE [reason_type] = [cmb_reason_type]"
  5. Me.[cmb_reason].RowSource = strreden
  6. Me.[cmb_reason].Requery
  7. End Sub
  8.  
This works fine ! However, only for the first reason the user puts in. If the user puts in a line, automatically a new record is shown in the subform.

When the user tries to put select a nuw type of reason from [cmb_reason_type], all previous [cmb_reason_type] comboboxes turn to this new value, which is NOT wat should happen.

How can i make it so that a new entry does not effet previous entries ?


Any help would be greatly appreciated !
Regards,
Pierkes.
Aug 22 '14 #1
9 2443
twinnyfo
3,653 Expert Mod 2GB
Hopefully this is a simple solution:

Line 4:

Expand|Select|Wrap|Line Numbers
  1. strreden = strreden & "WHERE [reason_type] = " & [cmb_reason_type] 
  2.  
Aug 22 '14 #2
Pierkes
64 64KB
Hi Twinnyfo,

Sorry, didn't work at all. All it did was mess up both comboboxes.

Any other suggestions ?

Best regards,
Pierkes
Aug 22 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Describe "mess up both combo boxes"--as we are not "doing anything to one of them at all.

Also, just realized a tweak may be needed to the code in Post #2:

Expand|Select|Wrap|Line Numbers
  1. strreden = strreden & "WHERE [reason_type] = " & Me.[cmb_reason_type]
That should make sure it is looking at the value of the control.

BTW, I have used the identical process you are trying to use. It works flawlessly--as long as you have the code referring to the proper controls.
Aug 22 '14 #4
jforbes
1,107 Expert 1GB
If I understand your post correctly, the Subform’s Default View is set to “Continuous Forms”. On a Continuous Form, even though you may see 10 or 12 instances of a Combobox (one per each row shown), all the instances of the Combobox share the same properties and events. So changing the properties for one instance will change them for all the intances.

I know… not what you wanted to find out. Don’t fret you can still get this to work. Move the code that is doing your work to a new Sub, like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub requeryReasonType()
  2.     Dim strreden As String
  3.     strreden = "SELECT * FROM Q_reason
  4.     strreden = strreden & "WHERE [reason_type] = " & Me.[cmb_reason_type].Value
  5.     Me.[cmb_reason].RowSource = strreden
  6.     Me.[cmb_reason].Requery
  7. End Sub
Then add the following to both cmb_reason_type_AfterUpdate() and the Form’s Form_Current() event:

Expand|Select|Wrap|Line Numbers
  1. Call requeryReasonType()
This way it will refresh the Reason Type list as you move across records.

I would also keep Twinnyfo’s suggestion in place as it is a cleaner way of doing it.
Aug 22 '14 #5
Pierkes
64 64KB
Hi Twinnyfo and jforbes,

Tried your suggestions but somehow i cannot get it to work.
I try to explain what happens;

The first combobox(cmb_reason_type) works fine, also when i have multiple records. It will keep its value record by record which is good, no problem there.

However, when i enter the form, the second combobox (cmb_reson) will show empty (which, when i leave out the code in the FORM_CURRENT() event, it will show all entries in the database just fine.

However, when i click on one of the first comboboxes (continous form, so i see a combobox for each record)all cmb_reason comboboxes seem empty. When i then choose an item from the cmb_reason_type combobox, it will only show up in the cmb_reason comboboxes where reason_type is the same.

Sounds logical to me, but how can i get it so that it eefects only the record i am working on at that time ?

Hope to hear from you !
Pierkes
Aug 23 '14 #6
NeoPa
32,556 Expert Mod 16PB
See Why Values in Unbound Form Controls do not Persist.

Your reference must be to the related object rather than the value of that object.

So, rather than redoing the SQL every time you need to set the SQL up originally to filter on the ComboBox by reference.
Aug 24 '14 #7
Pierkes
64 64KB
Hi NeoPa,

Thanks for your reply. However, i do not know how to do what you suggested.
Could you give me some more direction on how to, every time I need to set the SQL up originally to filter on the ComboBox by reference ?

Thanks in advance !
Pierkes
Aug 25 '14 #8
Pierkes
64 64KB
Hi NeoPa and others,

I was playing around with my code and found something interesting...

Wit combobox [cmb_reason_select] I use column 1 as bound column. I have 4 columns with a width of 0cm;4cm;0cm;0cm

Now, I tried to let it see one extra culumn, so ; 2cm;4cm;0;cm;0cm

This way the bound column is NOT 0cm wide. (I tried changing the bound column to a different column but that didn't do anything)

And guess what happened.....the combobox showed, next to the [reason] field (in column 2) the [ID_reason] field (which is a number and stored in the table [tbl_reason_dbase]....which is what i wanted.

I found out that, now i did this, the [cmb_reason_select] combobox keeps showing the number of the reason choosen (which is great !) , even when i change the combobox [cmb_reason_select] in the next record...

Hence, this is exactly what i wanted exept for one thing.
The combobox now only shows the stored number of the reason and does not show the reason itsel (text from field [reason])

How can a adapt i so that it stores the number and shows the reason ?

Any suggestions ?
Pierkes
Aug 25 '14 #9
NeoPa
32,556 Expert Mod 16PB
I don't know what the different columns are, of course. Nor do I know which one you need it to use.

All I can tell you is that the .Value of the control reflects the column you've set as bound - regardless of which of them are visible. The only value to show when the control has been navigated away from is the first one whose width is non-zero.
Aug 25 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: sparkle | last post by:
Hi Everybody, I'm filling a combobox from a class, which works fine on it's own. But when I insert code to fill in other controls something in the combobox fill is causing the...
1
by: Mariel | last post by:
Hello, I hava a main form F1, form with a subform F2 in datasheet view. There is a combobox in the main form C1, and a combobox C2 in the subform. I want the dropdown options of the combobox...
2
by: tshad | last post by:
In my VS 2003 Windows Forms page, when I initially fill my ComboBox (SystemList), it goes to the SelectedIndexChanged event which calls the Loademails() function. I then call it again in the...
1
by: z.ghulam | last post by:
Hi, I have a subform in a main form. When I create new records in the subform I would like the Subform. to autofill with the MainForm. It sounds quite simple, but ive been bustin my brains...
17
by: OldBirdman | last post by:
I have a form, bound to tblMain. I have tblSub, which is related to tblMain with a foreign key, all nice and proper database design. I want to display the rows of tblSub in a control, but neither...
1
by: chester64 | last post by:
Hello, I have created a form with 2 comboboxes (Employee_ID & Order_Num) and 1 Subform (sfOrder_Detail). The way I'm trying to make it work is like this ... you select the Employee in the...
1
by: sirdevo | last post by:
There is one combobox on a subform named Report Soil Series Subform1. The combobox is named cboSoilSeries. I need to disable this so the user can't enter values. I could also disable the whole...
5
by: hbaf208 | last post by:
I have a combobox on a subform that is based on an SQL that uses a listbox on the unbound parent form as the criteria. When the form is first loaded, it works perfectly, limiting the dropdowns to...
10
by: Merlyn | last post by:
Hi, I have a form with a subform. On the subform a combobox is shown. It contains over 500 items. I want to offer the users to find results containing the entered letters. (Eg. when entering...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.