PDA

View Full Version : Solved: Passing Userform data accessed from Database



mouckster
07-21-2006, 05:39 AM
I have a userform that allows the user to select items from a database. Each selection contains 5 fields and there are 10 selections to be made. Once the user completes the form shapes are inserted into a powerpoint slide.
My dilema: Everthing seems to work (kinda) however each group of shapes has a different combination on data. i.e. shape 1 will have data from field 1, 3, 5 shape 2 info from 1 only etc etc. There doesn't seem to be a pattern and all of the code was simply duplicated. Each shape range was given a unique name.
I'd post the code but it's way too long with way too may references, just hopeing to get ideas.
Sorry for the long post.

Killian
07-21-2006, 08:05 AM
Hi and welcome to VBAX :hi:

More information required...
You say what's going wrong (kinda) but you don't explain the desired behaviour.
I seems to me that you insert a number of shapes onto the slide and they're supposed to contain some or all of the data as text. The problem is associating the right data with the right shape???

I've attached a basic example of what I think you're getting at... am I close?

mouckster
07-21-2006, 08:55 AM
That is exactly what I'm doing, minus the access connection part. When I simply define variables as strings and pas the text through it seems to come in fine, however when I use the data from access things seem to go haywire. The code for each item is identical, however different text boxes are getting date, i personally think this is really wierd.

I can't really upload a sample because it's for work. However here is how it works basically.


Set shp = CreateTpl(leftPos, topPos, Layout10up.cboValue1, Layout10up.cboProdSec1, _
cboDate1, Layout10up.cboRole1, Layout10up.cboLogo1, sldPpt)

The Function CreateTpl looks like


Function CreateTombstoneTpl(ByVal leftPos As Double, ByVal topPos As Double, _
cboValueText As Control, cboProdSecText As Control, cboDateText As Control, _
cboRoleText As Control, cboLogoText As Control, sldTmp As Slide) As Shape



Set shp = sldCurrent.Shapes.AddTextbox(msoTextOrientationHorizontal, leftPos - 0, _
topPos + 1, 108, 20)
With shp
.Name = "amt"
With .TextFrame
.VerticalAnchor = msoAnchorMiddle
.HorizontalAnchor = msoAnchorCenter
.MarginBottom = 2
.MarginTop = 2
With .TextRange
.ParagraphFormat.Alignment = ppAlignCenter
.Text = cboValueText.Text
With .Characters.Font
.Name = conFontNameArial
.Size = 8
.Bold = msoTrue
End With
End With
End With
End With


Thanks for your help.

Dan.
Edited 27-Jul-06 by geekgirlau. Reason: insert line breaks

Killian
07-25-2006, 04:54 AM
Hi Dan,

You seen to be passing a lot of arguments for the fields and only using one. Maybe that's just for the example..?

A couple of points:
Rather than passing the control itself as an argument, it might be better to pass it's current value as a string Function CreateTombstoneTpl(ByVal leftPos As Double, ByVal topPos As Double, _
cboValueText.Value As String... etc
Each shape should have a unique name. The easiest way is to append the IDWith shp
.Name = "amt_" & .Id You can still find your shapes later if you need to by looking for those with names that start "amt_"
Edited 27-Jul-06 by geekgirlau. Reason: insert line breaks

mouckster
07-25-2006, 05:13 AM
Thanks for your help. I seem to have blundered into the solution (although it still doesn't make sense to me why the first way did not work). The Fields I was filling in for the form were List Boxes, When I changed them to text boxes any anomolies were gone.

Thanks again for your help. Being new to VBA all the help I can get is great.

Dan.

Killian
07-25-2006, 07:10 AM
The Fields I was filling in for the form were List Boxes, When I changed them to text boxes any anomolies were gone.This is why I suggested passing a string to the function (having isolated the listbox selected item). Somehow, it looks you don't always get the right item when you pass the control.
Curious.

mouckster
07-26-2006, 09:18 AM
I did switch it to passing a string through to the function however this did not really make any difference, still wasn't passing the values properly.

It wasn't that the wrong item was coming through, it was that some items didn't come through at all.


Set shp = CreateTpl(LeftPos, topPos, Layout10up.txtValue1, Layout10up.txtProdSec1, _
Layout10up.txtDate1, Layout10up.txtRole1, Layout10up.txtLogo1.Text, sldPpt)


This is the basic code used now, this code is repeated different times changeing LeftPos and TopPos. Layout10up is the form. When it was lst.Date1 three of 10 values passed. Once I changed the controls to textboxs 10 of 10 values passed. I don't know why, but I'm just happy it works.

For this same form I would like to reset the fields once they have passed.
I tried inserting

For i = 1 To Layout10up.Controls.Count
Me.UndoAction
Next i

but this only reset the dropdowns I'm using to select the values not the form textboxes used to display the info to the user.

Any Ideas?
Edited 27-Jul-06 by geekgirlau. Reason: insert line breaks

mouckster
07-26-2006, 09:35 AM
Yep, now I feel stupid. It would be hard to put it in much plainer english.

unload layout10up
Thanks for all your help.

Dan.