help with placing data on form 1

[docliv]

Hi, this is driving me crazy, I've posted several times with different attacks on the same problem and still I have no solution. I think I could solve my problem if I could just get data from my table on the form in the "form" I want it to be. The Problem...I have a table with the data I need to be on the form. This data is in one field. I can put the field on the form..no problem. Then I would like to put the same field right beside this one using data from that same field but from a "different" row.
For Example if my table was...

Field x
abc
def
ghi
jkl
mno
pqr

How can I get a form to look like

abc jkl mno pqr

or any other combination or order I need...Is this impossible..Maybe it's simple and I'm to new at Access to see how. I've tried crosstab queries, multiple tables, and a host of other things but I am stuck on this problem..and it shouldn't (in my opinion) be a problem. Do I need some kind of coding or cursor control to reference the field and row number to the field placement on the form. I have a table with 600 rows of data in one field that I would like to see (all) on a form. Please help with any ideas..I would really appreciate it.. It's driving me nuts. If it can't be done, I need to know that also.. Access surely has some kind of SQL or VB coding that would step thru a table and put this data on a form..
Thanks in advance for any help.

Terry

 

[BillPower]

This function is a variation of a function I found on Microsoft’s site earlier.

To see the original code go to

http://support.microsoft.com/default.aspx?scid=KB;en-us;q182822

Before using this, make a backup of your database.

Paste the following into a global module:

Function Transposer(strSource As String, strTarget As String, _
intHowManyFields As Integer, intSourceFieldPosition As Integer)
Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As Recordset
Dim i As Integer, j As Integer, k As Integer, intPosition As Integer
Dim intRecordCounter As Integer
Dim booEOF As Boolean

On Error Resume Next
Set db = CurrentDb()
DoCmd.DeleteObject acTable, strTarget 'delete target table

On Error GoTo Transposer_Err
Set rstSource = db.OpenRecordset(strSource)

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To intHowManyFields - 1 'add specified amount of fields -1, starts at 0
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef

' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
'intSourceFieldPosition, starting at 0
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(intSourceFieldPosition).Name
.Update
End With

rstSource.MoveFirst
rstTarget.MoveFirst
' Fill the target table fields with data
' from the source table.
For i = 0 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(intSourceFieldPosition)
rstSource.MoveNext
.Update
End With

Next i
rstTarget.MoveNext

While booEOF = False And Not rstSource.EOF 'while not last record
rstTarget.MoveFirst
' Fill the target table fields with data
' from the source table.
With rstTarget
.AddNew
For i = 0 To rstTarget.Fields.Count - 1
If rstSource.EOF Then
booEOF = True
Exit For
End If
.Fields(i) = rstSource.Fields(intSourceFieldPosition)
rstSource.MoveNext
Next i
.Update
End With
rstTarget.MoveNext
Wend
db.Close
Exit Function

Transposer_Err:
Select Case Err
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select
Exit Function
End Function
----------------------------------------------------------

To call the Function put the following in your form’s on load event:

Retval = Transposer("Demo", "Demo1", 6, 1)
----------------------------------------------------------

1. Replace “Demo” with the name of the table with the records you want to view.
2. Replace “Demo1” with the name of a table to ouput the transposed records to.
3. Replace the 6 with amount of fields that you want to view.
4. Replace the 1 with the position of the field that you want to transpose your records from, the 1st field is 0, the 2nd 1, etc.

On your form you will need to set up the same amount of text boxes you specify at 3 above. Leave them unbound.

In your form’s on load event:

Me.Recordsource = “Demo1” Replace this with the name you have called the table at 2 above.
Me!txtText1.ControlSource = “1” Replace txtText1 with the name of your control for field 1
Me!txtText2.ControlSource = “2” Replace txtText2 with the name of your control for field 2
Repeat this for the amount of fields selected at 3 above.
Set your form to Continuous.
When you open the form you should be able to view row by row by however many fields specified at 3 above

If you would like to see this working, e.g. the database I did this in, let me know and I’ll put it on my site for download.

The limitation of this Function is at the moment the records come out in the order that they are stored in your table. If you have some sort of flag/identifier or can do this against each record, then maybe next weekend I can help you develop this further. Basically, you need some criteria.

Regards

Bill

 

[docliv]

Bill,
Thanks ever so much for your help. One question I have before I give this a spin...will this work in my case since I have about 626 rows of data in one field that I want on the form. If this function turns the rows of data info fields in a "dummy" table, will I run up against the 255 field limitation (again). What I am trying to do is just put "one" field on the form, but put it on there many times until all rows of this data are visible on the form at a glance. Again, thanks ever so much for your help. I am constantly amazed at the talent located in these forums. I hope you guys are making the "big bucks"

Terry

 

[BillPower]

Simply, YES.

This is Item 3 above, if you select say 10 fields here, you will get the 1st row of 10 columns (fields) showing the 1st 10 records from your table, the next row will display the next 10 records and so forth.

The most important thing you do before giving this a spin is to make a copy of your database, though I promise you it will work as long as you follow the instructions.

Bill

 

[BillPower]

Hi again docliv,

Just to explain this function a bit more.

As I said I got the original code from Microsoft's site, but added some flexibility to it.

Function Transposer(strSource As String, strTarget As String, _
intHowManyFields As Integer, intSourceFieldPosition As Integer)

Using Microsoft's code as is, would give you 626 fields as you seemed concerned about.

intHowManyFields tells the function to only create the fields/columns that you specify, the function then loops thru a procedure transposing lets say, 10 records at a time until the last record is reached, then exits the loop.

intSourceFieldPosition tells the function to get its data from the column that you specify, if you only have one field in your table, intSourceFieldPosition would be zero, always deduct 1 from the field's actual position, from left to right.

That's it.

Regards

Bill

 

[docliv]

Billpower,
Thanks ever so much for the code and the explaination of same. I can't wait to give this a spin.
You might consider posting this info as a FAQ here, since I haven't seen any post addressing this (I did look at FAQ's and ran several keyword searches before I posted). At any rate, I marked your post as a helpful/expert post, and again, Thank You

Terry

 

[docliv]

Billpower,
I hate to bother you again, but I am (thru my ignorance I'm sure) having troulbe getting your code to run. I keep getting a ByRef agrument type mismatch and if I get by this I get a compile error (centered on the Dim db as database "statement&quot The help file said to check my references to make sure I had the right librarys checked. I guess my quick question is: Do I need to do something different to run this in Access 2000? Am I not pasting in the code correctly (I just open a module window and pasted directly in)? I can seem to get it to work in the immediate window when I type in ....? Transposer("mytablename","dummytablename",10,1)
Any ideas you might have would be appreciated. I would also be interested in downloading your sample database where you used this procedure. Again thanks in advance for help

 

[BillPower]

Hi docliv,

It sounds like you need to install Microsoft DAO 3.6 Object Library.

To do this, open any module, from the menubar, select TOOLS|REFERENCES if you can't see the above ticked, scroll down until you can see it, tick it. Should work now.

Let me know.

Bill

 

[BillPower]

I have posted a working demo of the Transposer Function at

http://www.wpower.fsbusiness.co.uk/

Transposer.zip.

Included is a demo form that shows you exactly how to call the funtion and reference the form's controls, when the form loads.

Also you will be able to view which references are selected under TOOLS|REFERENCES.

Bill

 

[docliv]

Billpower,
Thanks for the download of transposer..I am still getting an error when trying to open the form..When I look at the references, I see a Missing: Microsoft ADO ext. 2.7 for ddl & security in the list. I am running win98 with office 2000 (including access 2000). Do I need a download somewhere? the missing file seems to be msadox.ocx, do you know where I can get this. Again, thanks for all your help

 

[BillPower]

Hi docliv you can find a copy of msadox.ocx in msadox.zip posted on my site at:

http://www.wpower.fsbusiness.co.uk/

Extract the file msadox.ocx to c:\windows\system.

Go into TOOLS|REFERENCES, find Microsoft ADO ext. 2.7, click on the browse button, the directory will be "c:\windows\system" Select ActiveXControls from the file types. You should see msadox.ocx, double click on it to reference it.

I'm not going to say it'll work this time, it seems like bad luck.

Let me know how you get on. I'm amazed that you haven't got msadox.ocx on your PC though.

Bill

 

[docliv]

Bill,
Just a quick note to let you know I got everything working with your Transposer code. It is a great piece of progamming. I got my data into a 25 by 25 grid like I wanted using your transposer to make 25 fields. I don't know if access is going to let me do what I wanted to do originally. My 25 by 25 grid of "data" is actually all "1"'s and "0"'s to equate to either "on" or "off". I was using option buttions to make a black dot for "on" and empty circle for "off". My problem is going to stem from having to use the continous form view to display data. Using this view, I can't label each row differently. That's why I was trying to use one view of the form to display the 625 "pieces" of data, and that's why I was running into the 255 (or whatever it is) field limitations. I tried using transposer with a higher field number and it looks like it is subject to the same limitations, but that is no surprise. Short of any major revelation (on my part), I think I will look into porting the data to excel and constructing my 25 by 25 "option button" grid there.
Again, thanks so much for bearing with me thru this. I am constantly amazed at what talented programmers can do. I am going to put "taking a VB class" on my list of things to do before I die.
Again, thanks.. Wishing you and your family a very happy Holiday season.

Terry Livingston
Paoli, Indiana