Define the combo box

[radek225]

Hello I'm new in this forum and I'm beginner in VBA.
I have some problem in my database and I hope that you can help me guys.

The form name is "MyForm" but I think It doesn't matter.
I have a 2 subform (I will describe only the fields with which I have a problem):

1. Subform "x" (Multiple Items)
-Field "MyName" (text) and "together" (Yes/No)
2. Subform "y" (Multiple Items)
-Field "Myname" (combo box)

Combo box has the same tasks which are in the "x" subform in "MyName" field.
Is it possible to do, that when I select at least in two rows field "together" in Subform "x" then in combo box in Subform "y" I should have additional task which is the sum of the names?

e.g.
In Subform "x" I have 3 positions
"a"
"b"
"c"
And in position "a" and "c" I select "together" (Yes/No), so In combo box "MyName" in Subform "y" I should have taksks like this:
"a"
"b"
"c"
"a; c"

Please guys help me

 

[nschroeder]

I'm not usually very good at deciphering what people are trying to say & do, and I'm a little vague here, but I'm not sure you're understanding the purpose of forms and subforms. Generally, the main form (form1) has a table (table1) as its datasource. The primary key to table1 will have unique values. Table2 will have a field linked to the table1 pk, and it can have multiple records with duplicate values in that field, but a second field combines with it to make it unique. The subform (form2) has table2 as its datasource, and will show the related records to the current record on form1. (Example: Salesman on form1/table1, and Sales on form2/table2). Maybe I'm getting sidetracked, but since you have MyName as a common field on your two subforms, it's not making sense that you would have different values in the MyName field on two different subforms.

 

[radek225]

That's what I wrote it's only example but in real:

I have a form which is about order:
Date order, name, client, technology of print..


First subform it's about product part, like:
1) "leaflet about cars", two colors, 10x10 cm2
2) "folder about cars", one color, 21x29,7 cm2
3) "cards" two coloros, 50x90 cm2


Second subform is about paper and tips of print. It is right that combo could has the same name like:

1)"leaflet about cars" paper 700x500, single side, 5
2) "folder about cars", paper 630x430, work and turn, 3
3) "cards", paper 500x430, work and turn, 12

But It can looks like this:

1) "leaflet about cars"; "folder about cars", paper 700x500 work and turn 2
2)cards, paper 500x430, work and turn, 12

Do you see the difference?

 

[nschroeder]

Still not making much sense to me. They way you've described it, the table behind the second subform would have 3 fields:

Description (leaflet about cars)
Paper size (700 x 500)
Print method (single side)
Print num (5)

And then you show three records as examples, but in the second example, you show the 1st and 2nd record combined into a single record with two description fields (or maybe the two descriptions concatenated into a single description -- hard to tell), and the 2nd record has been deleted, but that would lose the other data from that record. You didn't indicate whether you understood what I was saying about tables, forms, and subforms, so I think we're not on the same page here.

 

[jdraw]

I'm not following your description either. Here are video tutorials dealing with Form and multiple subforms that may be helpful.
http://www.datapigtechnologies.com/flashfiles/subforms1.html
http://www.datapigtechnologies.com/flashfiles/subform2.html

 

[radek225]

Print num (5)

It is Numer of Fields


I prepared some preview.
Technology of print doesn't matter. The key is Number of Fields
If You open images then You can see, the second case is different because has two orders in one paper print - that's the point.

 

[pr2-eugin]

is it possible if you could upload a stripped down (dummy) version of your DB? with some sample data and the forms in question.. So we can have a loot at?

 

[Deekay]

I think I may understand - apologies if not.

It sounds like you need to combine data from two sources. Can you therefore create a query with the data from the two parts. Use the query to create a calculated field that cocatenates the other fields to show the data you want and then base your combo box on this query and this calculated field.

Does this help?

 

[radek225]

Sorry for the late reply.
First of all, guys thanks for insight.
I prepared stripped down version. Only fields with red color are important.
Deekay, You have right:

It sounds like you need to combine data from two sources...

but I don't know how to do this.

All I want is combo box in Y subform which can combine data from two sources, and has a list of rest parts in this order.

 

[Deekay]

OK...
I'm assuming that the two parts of your data are in two separate tables (or queries).

1. Go to the query designer page and add the two tables (or queries). If there is any fields which link the two create a link between the fields.
2. Now add the fields that you wish to combine into your combobox plus any other fields which uniquely identify the records
3. Now cocatenate two or more fields together (say [firstName] and [secondName] by going to first free column after your fields and adding a new name - anything will do - say 'combinedData', followed by a colon : then the two field names with a '&' between. so instead of the field name in the query you write combinedData:[firstName] & [secondName].
4. If you want a space in between the two fields when it appears in the combo box then use the syntax combinedData:[firstName] & " " & [secondName]. (there is a space between the quote marks). Add other field names in the same way.
5. It is important that this calculated field appears AFTER the other fields upon which it is drawing.
6. Now save the query with any suitable name.
7. Now use this query as the row source of your combobox, selecting the 'combinedData' field as the source. The 'control source' field will be any unique field that you have added to your query.

There is an alternative to the cocatenation and that is to create the query without the calculated field and set the combo 'columns' property to the number of fields you want to show + 1. Put the unique 'control source' field as the first column. Then set the 'control source' field to column 1 and in the column width property, set the first column width to 0.

If this is confusing you need to look at an example combobox that already works and has more than one column.

The biggest challenge for you is how to capture the choice with one unique field - and without seeing your database I can't tell you that.

Hope this helps.
Deekay

 

[radek225]

Thanks for Your reply.
I created example base (attached above your post).
In Your description I can't see relation to "togeother" field what I described or It's to hard for me:/. Could You see my attached in my last post?

 

[nschroeder]

DeeKay, what you're doing concatenates values from two fields into one. I think he's wanting values from the same field in different records.

Radek225, if the "JobName" field in your forms is same as the "MyName" field referred to in your original post, then it's not a combo box, it's a text box. If so, that's been throwing us off-track all along. Combining values into a text box doesn't make it a combo box.

Based on those assumptions, I put some code together to try to accomplish the task with a command button. However, when I click the button, I get "The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control". I've checked references and they all match up with my other apps. I also tried removing and adding the references back, and did a compact & repair. I also tried adding the code to the Click or Double-click event on a text box with the same result. It's not in the code -- the problem occurs with an empty event. Can anyone get me past this so I can test the code and maybe provide a solution?

 

[radek225]

Radek225, if the "JobName" field in your forms is same as the "MyName" field referred to in your original post

Yes

1)I know that it isn't combo box, I didn't make combo box because I don't know how to create combo with values what I described. But You have right in subform Y should be combo box instead text field.

2) In my exaple what I attached, are 3 fields with red color. All I want is that positions("Job Name"text fields, subform x) which are marked "togeother" options, should be combined in one data ("JobName", combo box, subform y ).

 

[nschroeder]

Could someone please open his sample database and create and trigger an event on the form to see if you get the same error I got above? It seems specific to his forms. I created a new blank form with a button and a click event, and it ran fine. I also copied his tables and forms to a different database and still get the same error. I'm happy to help him, but am stuck at this point.

 

[nschroeder]

Rather than remain muddled by the run-time error, I started over and created three new forms to test the code, and I think it will do what you want. I put the code in the double-click event of JobName on form Y, but you could put it elsewhere, such as in a button click.

Private Sub JobName_DblClick(Cancel As Integer)
Dim db As Database
Dim rsJobs As Recordset
Dim strSQL As String, strResult As String

    Set db = CurrentDb()
    strSQL = "SELECT tblX.JobName " & _
             "FROM tblX " & _
             "WHERE tblX.ID_Order=" & Me.ID_Order.Value & " AND tblX.Together=Yes;"
    Set rsJobs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    With rsJobs
        Do Until .EOF
            strResult = strResult & !JobName & "; "
            .MoveNext
        Loop
    End With

    Me.JobName.Value = Left(strResult, Len(strResult) - 2) ' Remove trailing "; "

    Set rsJobs = Nothing
    Set db = Nothing
End Sub

 

[radek225]

I still have an error. Can You attach your example database? I analize it and see what I'm doing wrong.
Thx!

 

[gemma-the-husky]

i think part of the problem is that your design may be unusual, which may be why it is causing confusion

generally forms and subforms should be realted in a hierarchical way

so if a main form, has 2 subforms, A and B - then A and B should be independent of each other, and changes to anything on A should have no effect on B.

so if in your case subform B is really dependent on A, then the form ought to be

main form (with sub form A (with subform B))

I hope that makes sense

even in this scenario, the relationship is always downwards. Changes in the main form will reflect in subform A, which will then reflect in subform B. changes in A will reflect in subform B.

the relationship should never be upwwards, so that changes in a subform should really have no effect on parent forms, or by implication on other subforms. If you do have such a situation, then probably your design could be changed to remove this apparent dependency. ie - changes in subformB should have no effect on subformA, or the parent form.

I suppose this view might be too "fixed", and there may be circumstances in which a complex relationship is unavoidable - but most of the time the design probably needs reconsidering.

 

[nschroeder]

Whatever redesigning you do is up to you. However, I've attached a db that will accomplish what I think you requested. In order to get around the errors I described earlier, I pretty much started from scratch with the forms, so they're named a little differently, and I didn't include all the fields or position them the same, but you should get the idea. Selected the desired checkboxes in frmX, then double-click in JobName on frmY to pull in the combined results. Have fun!

 

[radek225]

Great! I tested it, working. I'm very grateful for your help!!!