Get Each Record Name by VBA

[ice051505]

Hi, Can anyone tell me how to get the different record name in one column? I have a query which only has one column and I hope to use some kind of rs.Record(1).name, rs.Record(2).name... to get the row value by using the VBA code. All the values in this column are text format, and the name and number of records in this query are depends on how user select on the form. I couldn't find anything related online, so hope anyone can help me little bit here

Thanks

[June7]

Do you want to concatenate values from multiple records to one field? Review http://allenbrowne.com/func-concat.html

[ice051505]

Nope, I just want to pull out each single record and to make each single record as the column variables, just like using the crosstab query, but I need the to use the query as control source on the Edit Form, which crosstab is read-only. So I plan to use the statment such as

set rs = OpenRecordset("AreaGroup")
recordCount = DCount("travelArea","AreaGroup")

For intX = 1 to recordCount
SQL = "SELECT Date, Group, (IIf([selectionQuery].[Area]= " & rs.record(intX) & ", [NumberOfpeople],"-")) As " & rs.record(intX).name & " From selectionQuery, AreaGroup;"
qdf.SQL=SQL
Next intX

So I need to get the each individual record from the "AreaGroup" Query, which only contain one column of the selection areas

Please help me...I struggled almost one week on this piece of code...

Do you want to concatenate values from multiple records to one field? Review http://allenbrowne.com/func-concat.html

[June7]

You want to build an sql statement to use as the RecordSource for a form? If you want an editable recordset, not seeing how this code will be any better than a crosstab query.

You open a recordset so there is no need for the DCount function. Use the Recordcount property of the open recordset.

Why is there no JOIN clause in that sql?

I really don't understand what you want. Show example of source data and example of desired output.

[ice051505]

OK! Here is the dataset from the "selectionQuery", which select data from Form:
Date Group Area NumberOfpeople
07/01 Adult AB----44,934
15/01 Adult BC----87,233
15/01 Child BC----23,158
23/01 Adult ON----59,234
23/01 Child AB----13,981
......
The outcome I want is a editable query or form which sort the data as below
Date Group AB-------BC------ON... (Note: the area group may different base on the different selection on the Form)
01/07 Adult 44,934
15/01 Adult--------87,233
15/01 Child---------23,158
23/01 Adult-----------------59,234
23/01 Child 13,981
.....
Crosstab can give me exactly result but it is uneditable, Do you know a way to make it editable or any other solutions? Thanks!

You want to build an sql statement to use as the RecordSource for a form? If you want an editable recordset, not seeing how this code will be any better than a crosstab query.

You open a recordset so there is no need for the DCount function. Use the Recordcount property of the open recordset.

Why is there no JOIN clause in that sql?

I really don't understand what you want. Show example of source data and example of desired output.

[June7]

No, doesn't matter which way you go, the data is manipulated into an unnormalized, uneditable dataset. There is no table with this structure to edit.

I suppose could use a bunch of VBA code to write data into and out of unbound form but that is a major headache.

Why would data entry need to be done this way anyhow?

[ice051505]

My supervisor asked this format and it is easy for him to check and change the data value in the future time. Anyway, Could you give me more detail on how to do this with VBA code? I have bunch of unbound labels and txt box in the result form when I try to use crosstab query. Do you mean to use crosstab query as a control or restriction, and then use the data from the table as the control source of the unbound form to edit data into the table directly?

Thanks a lot!

No, doesn't matter which way you go, the data is manipulated into an unnormalized, uneditable dataset. There is no table with this structure to edit.

I suppose could use a bunch of VBA code to write data into and out of unbound form but that is a major headache.

Why would data entry need to be done this way anyhow?

[June7]

Crosstab query plays no part in this.

I have never used unbound forms for data entry/edit. Think I would rather fight the walking dead!

Google: Access vba unbound form

Review this tutorial for starters http://www.databaselessons.com/unbound-forms-1.php

[Parsonshere]

Let's do this with a form.
Note:I am assuming each record contains all the groups
With the Form,
recordsource = AreaGroup
Add only one field bound textbox: fldArea
rename the textbox txtArea, so it's not the same as the field name.
place one listbox on the form.
Name it lstArea.
Caption on lstBox label: Select Area
set listbox properties as follows
rowsource = "Select Distinct Area from AreaGroup order by Area"
rowsourcetype = Table/Query
column count: 1
control source: leave blank
On Click: "Event Procedure" Note: Cause it to create the private sub in vb window, but leave it blank for now
Save the form as frmMain and close it for now.
Use wizard to create a continuous form to use as a subform for frmMain.
Base the form on the areagroup table
Select tabular during the wizard
Add txtDte txtGrp txtCnt Note: Do not add area here, (or hide the area textbox if 2003 requires that), since the area field will show on the main form - Area will be selected from the listbox
set the orderby to: Date;Group
set orderby on load to true
Rename each textbox so Access doesn't confuse textbox names with field names.
Note: Do not add the area field, that is determined by the listbox
save the continous subform and close it
open frmMain in design mode and add the subform using the wizard
Use the Area field as the master and child links
Now set the Event Procedure of the listbox to look something like this:
Private Sub lstArea_click
dim x
with me
x = .lstArea
.txtArea.setfocus
end with
DoCmd.FindRecord x, acEntire, , acSearchAll, , acCurrent, True
end sub
The above may need dressing up, or may just get you started.
Obviously, you will want to bind the form to a table.
I hope it helps.

[orange]

You may get some ideas from this free video and get an updatable recordset.
It may not apply, but worth a look.

[ice051505]

Thank you guys!!! I will give a try, Thanks a lot for all the informations, once I figure out, I will post my solution here for anyone who may have same problem as me :P

[ice051505]

Hi orange, this is the way I intend to do instead of the crosstab query, but my problem is I don't know how to make an updatatable recordset, and I want to know how can I get the distinct value from area field where the number of record and value of record are selected from a form.

Thanks!

You may get some ideas from this free video and get an updatable recordset.
It may not apply, but worth a look.