Add unbound row to form

[94Sport5sp]

Hi:

I have created a continuous form bound to a query (X-Tab) where the detail row is a series of unbound text boxes. I then wrote some vba to populate the detail row from the query recordset. (The vba code also populates the heading row which are also unbound text boxes.)

Everything works fine except I do not know how to add or reference a new detail row when a I move to the next record in the recordset, and as a result my form shows me one row for each record repeating whatever data was in the ending record.

So my question is: How do I populate the detail row so that one detail row matches one record from the recordset.

Some background. Each unbound control in the detail section is named "Col" and then a sequential number. So I have Col1, Col2, Col3, etc to Colx (the maximum field the query can return. I have done the same with the heading being Hed1, Hed2, etc to the maximum. Now I need to be able to reference Row1Col1, Row1Col2, Row1Col3, etc and then Row2Col1, Row2Col2, Row2Col3, etc to the maximum number of records.

Thanks

 

[vbaInet]

You can't have an 'independent' unbound control in a Continuous Form. What exactly are you trying to do with the unbound control?

 

[94Sport5sp]

Hi:

The X-Tab produces a varying number of fields and the first record from the X-Tab will be a label to identify what the values of each field represent. So I would like to display on screen for the user see the results from the X-Tab query. The form allows the user to select a date range (usually one month worth of data) and the X-Tab will produce one row for each day of the month. I would like to display that data in a meaningful manner for the user.

Thanks

PS: If I cannot do it with a form then I am looking for alternative suggestions as well.

 

[vbaInet]

Just one small thing, could you please write "Crosstab" because we're not used to seeing X-tab.

You've explained what you would like to present to the user but you haven't explained where the unbound textbox comes into play and what you want to do with it?

 

[94Sport5sp]

Just one small thing, could you please write "Crosstab" because we're not used to seeing X-tab.

Sorry: Old habits are hard to remove sometimes.

The Crosstab is summary of sales by day for each sales area. The Crosstab query field defined as column headings will become the field names the form or report will see. This means that the field names produced by the Crosstab could be different every time the Crosstab is run, or the order might be different, or might not be present in subsequent runs. If I design a form bound to the Crosstab using the field names provided by the Crosstab then when the Crosstab data changes my form may not find that field.

So, I wrote some vba code to get the first record from the query recordset and assign the name of field 1 to Hed1 (my unbound header field) and value of field 1 to Col1 (my unbound detail field) with a counter set to the maximum number of fields from the Crosstab record I looped until all fields have been assigned to unbound field. Now I want to get a new record (not a problem) and assign the data to a new set of unbound fields (problem as it replaces the previous unbound data).

My form was designed (with unbound fields) to accommodate the maximum number of fields and those unbound fields that are not used a set to visible = false to create a tidy report. All of this works fine. If there is 30 days of sale my form shows 30 lines of information. If there is only 2 days of sales then my form shows only 2 lines of information. The problem is that the lines of sales shown are all the same showing the sales for the last day only.

So, for example, if I have sales areas East, West, North, and South and sales are for July 1 to 31 as the selected period. Then there will be a maximum number of 124 records (4 times 31) produced by the query. If the products I am selling is Cars, Trucks, and Vans then the field names associated with a bound column would be Cars, Trucks, and Vans. The recordset for the query would then return one record for each day (that had sales) for each area and a value associated with each field (Cars, Trucks, and Vans) that had sales. If there are no Van sales in any region then the Van field would not be part of the recordset and my form would looking for a field call Van which is not there.

Sorry if I went to detail and long winded, however, that is the problem I was trying to address. Once that problem is addressed (either because there is a way to do it or I have to find a different way to display the data) the form will also total the sales by product for the period selected and the user could filter out all sales except East or West or whatever is requested.

Thanks for your time

 

[vbaInet]

So as previously mentioned, unbound controls will not work as expected in a Continuous form. If you're having to deal with a fluid crosstab query then the following is what you should do:

1. Identify the fields that are present in the query by using a recordset
2. Open the form in design view
3. For each field in the recordset from step 1 set the Control Source of the controls on the form
4. Hide the remaining unbound controls
5. Open the continuous form in normal view.

 

[94Sport5sp]

Hi:

So my vba code would open the recordset and create/modify the form to match the fields in the query recordset and then then data would display properly?

Ok, I do believe I have read other help topics on creating a form from within vba. So then, back to the drawing board.

Thanks

 

[vbaInet]

No, not that. Please go through the steps again.

Let me elaborate. You would have already created the form with a bunch of unbound controls. The form will be bound to your crosstab query. All you're doing with the recordset is identifying which fields are derived from the crosstab query and using that information to bind your controls in the form. That is, you will set the Control Source property of the unbound controls whilst the form is in design view.

 

[94Sport5sp]

Hi:

I do not see how that would automate the process. If I understand you correctly I or the user would have to change the form each time the recordset changes.

Thanks

 

[vbaInet]

I best ask, do you know how to work with recordsets?

What step in the process are you not sure about?

 

[94Sport5sp]

Hi:

I have no problems creating forms (my knowledge is rusty so I do not remember all the tricks I used to use) and binding a table or query to the form and I have no problem binding a field to a control. I just need to make sure that the terms you are using fit with my understanding of how I remember the term.

So if your question is "Can I design a form, bound to a query and then bind fields from that query to controls on a form?" then the answer is yes I a comfortable with that. I can even figure out how to write vba code (although that is even rustier) to open the record set and manipulate the fields and values.

I this particular form the field names are subject to change (because of the nature of the Crosstab Query) and that is where I am having issues. How do I create a form to be dynamic. If the query returns a new field name (because an item which was not sold before is now being sold) how to I handle that without having to go back to the form design mode and add the field to the form? The form itself really does not change it is just that a new field was created by the Crosstab. So is there a way that with vba I can tell access that the unbound field (called Col23) is now bound to field "Sub Compact" (or whatever the new product name is) since the company is now selling Sub Compact cars?

A similar problem occurs if an item is discontinued and is no longer being sold. I want the form to not display that field without me (it cannot be the user) having to go back to form design and removing that field.

Thanks

 

[vbaInet]

This question has been asked a couple of times so I understand exactly what you're trying to do. I've already given the steps through which you can accomplish your task and the only manual step is creating a form with several unbound textboxes (whether it's 100 or 200). Everything else is automoted.

And it's not as easy as you imagine because you would have to worry about reposition of the controls, re-alignment and re-sizing... all to fit the inner width of your form.

 

[94Sport5sp]

Hi:

Ok, that sound promising. I have designed the form so that all the controls are the same size except for the first two controls. The first two are for date and sales area and will never change. So for now I have bound the controls to match the query and the form is saved and the form view displays the data I want.

Thanks

 

[vbaInet]

Loop through the unbound controls and for each field in the recordset, bind the control to the field name. Hide the other unbound controls. If you start implementing it from step 1 it will become apparent what I'm telling you.

 

[94Sport5sp]

Hi:

When you are blind it is hard to see the trees when you are standing in the forest. I have been fairly fixated on adding unbound rows to the form and then populating the rows with recordset data that I missed the really easy fix.

vbaInet finally forced me to open my eyes and the solution is simple. The form is designed with unbound fields. The vba code me("Col" & intCnt).ControlSource = fld.Name binds the unbound control to the field from the query. After that Access displays the continuous form and I have the report I want. I do a For loop for all the fields in the query and when finished hide any unused unbound controls.

Such a simple solution I do not know why I could not see it.

Thanks

 

[vbaInet]

Pleased to hear you got it working.