Dynamic SQL
I have a little problem....
I have 3 options on a frame and on the basis of which option is chosen I want to load a combo box.But I want to write only one SQL statement for them.
can i do something like this
if option1.value then
a=option1.caption
elseif option2.value then
a=option2.caption
elseif option3.value then
a=option3.caption
end if
sql.open "Select a from table1"
In this way i want to write only one sql query whatever the option is chosen.
PLease help.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Dynamic Forms With Dynamic Controls
I have an mdi form with a couple of child forms on it. these children forms are pre loaded with controls with an index of 0. i create an instance of these children forms and load some controls at run time. in some cases (like a label control) the controls are not visible. and in another it gives run time error 340 (control array element 1 does not exist).
Hope someone can tell me what gives this kind of error?
TIA
ferdz
Dimensionalized Dynamic Array Copied To A New Array Is No Longer Dynamic
I have a user-defined object DS, with a child used-defined object l() which is a dynamic array.
At some stage, I ReDim DS.l(x) where x is any positive integer and use the array.
I later do Let tempDS = DS and try to use the tempDS object and its property .l() in the same way. I'm trying to subsequently ReDim tempDS.l(y) ... but it doesn't work because the tempDS.l object was created as tempDS.l(x), not tempDS.l()
How can I make tempDS.l be created as l() and not l(x) when created by copying DS.l(x) ?
Script Control, Dynamic Control Creation, And Dynamic Control Events
Hey everyone.
I have been messing around with the Scripting Control and Dynamic Control Creation.. I think it's amazingly powerful to have a program that can be extended by an end user by using VB Scripting commands. I'm actually developing a system that will allow scripts to be written, saved, and run on start-up of my program to create any additional forms or controls that end users have created so it is as if they have been there the whole time.
My problem is this:
I am creating controls as follows:
I Created a class ScriptingFunctions and added some functions to it, instantiate the class, and add the object to the ScriptControl so that i can have full control of the functions.
I run a function called AddObjectToForm(ByVal sFormName as string, byVal sObjectType as string, ByVal sObjectName as String).
It loops through vb.forms, finds the form with the name you sent, selects it, and does the following:
frmForm.Controls.Add sObjectType, sObjectName
I then call, from script, a function already in the form called "GiveAccessTo" which adds the object with the form name as a prefix to the ScriptControl.. so if I just created a "Vb.CommandButton" with name "cmdNew", I can access it through the scripting control now using "frmFormcmdNew.Caption = "NewCaption""
My problem is, however....
How can I add code .. through the scripting control, for the events on the new control I just created?
I could see creating a new windowproc in the scripting control and doing a SetWindowLong w/ GWL_WNDPROC to subclass the window procedure, but I'm not sure what I would have to do with the function? Should I have a MsScriptControlCTL.Module and add the function to that, and reference that function via AddressOf to get the pointer to that function?
Don't forget, I know when the program ends, all of this will go away, but Like I said, I plan to have the script saved so it will execute on startup.
I'd really appreciate any help anyone could give me on this.
Thanks a lot in advance! :)
--NipsMG
Loading Dynamic Menus Within Dynamic Menus
We have a situation where some of our menus have become extremely long (upwards of 50 items) and are becoming unusable as a result. I've been asked to checnge our menu program so that, if there are more than 15 items it will group them into sub menus. Sound reasonably easy but I just can't quite seem to do it. I've been able to group the first 15 together by using the following algorithm:-
VB Code:
while moreRowsToAdd 'this is actually a while not eof statement against a recordset fnCreateMenu(rcsTmp("description")) 'this dynamically loads a menu item and returns it Let intMenuCount = intMenuCount + 1 If intMenuCount = mconMaxToolbarMenuOptions Then 'we need to add a sub menu and move all of the last 15 items onto it 'or we need to add a new menu at this level and continue to load into that one For lp2 = 1 To mconMaxToolbarMenuOptions 'get the new sub option menu Set mnuTmp2 = fnGetSubMenuItem(lp2) 'this routine loads a new sub menu item dynamically and returns it. 'get the existing menu Set mnuTmp = fnGetMenuItem(lp2) 'copy the details across Let mnuTmp2.Caption = mnuTmp.Caption 'unload the existing (and now unwanted) menu Unload mnuTmp Next lp2 Set mnuTmpHeader = fnGetMenuItem(intXpos, intYpos, 0, False) Let mnuTmpHeader.Caption = "Page 1" End Ifwend
So my menu structure at design time is to have an indexed menu item with an indexed sub menu item below it. Under normal circumstances it just dynamically loads as many menus as it needs. It always leaves menuItem(0) as blank and then hides it later in the program.
However, if it reaches 15 menu items it then starts loading sub menu items and copying the details of the current 15 menu items across. It then unloads the menu items as it no longer need them and renames menuItem(0), which now contains the 15 sub menu items, to "Page 1". This is fine and working so far.
The problem is that the next 15 items need to be loaded into Page 2, then Page 3 and so on. I can't seem to achieve this because Page 2 would presumably equate to menuItem(1), page 3 to menuItem(2) and so on. But at design time subMenuItem(0) can only exist in one place, i.e. under menuItem(0). So if I just keep loading subMenuItems they all appear under Page 1 which defeats the point of doing this in the first place.
I've tried creating menuItem(0) to menuItem(9) at design time and then creating subMenuItem0(0) to subMenuItem9(0) under them respectively but, since I need to do this with several menus, I eventually reach the point where VB reports tha I can't create any more controls on the form.
Does anyone have another suggestions?
UI And Dynamic UI
hey,
i'm a newbie in VB but i've already some questions:
1. i've made a user interface in photoshop, how can I use this in visual basic??
2. How can I make this userface dynamic such as what you can do in flash (fade in/out effects and some other dynamical stuff)?
Dynamic IP
o can i use a dynamic IP for a program am creating, i have set up the account and for privacy purposes lets say it squidge.no-ip.info (its not really)so do i just set the remotehost = sqidge.no-ip.info ?
if not how do i go about it?
Dynamic Var V2
Hi together...
Think i've found a way to describe my problem.
Code:
Dim xxx as String
xxx="Action=3"
Ok. thats all. But how can i EXECUTE this String IN a Variable in Runtime?
Something like:
Code:
execute(xxx)
MsgBox(Action) 'Should return 3
Response.Write(xxx) ' Doesnt work. Tried so many Things. I'm lost
xxx is DYNAMIC. Its in a do while function... every time he reads this in do while, he haves to execute it.
Please help :'(
Dynamic IP
I want to have a program run on my computer that allows remote connections, but I have a dynamic IP. I use:
Code:
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private IP As String
Private Sub Main()
Dim lngRetVal As Long, fn As Long
lngRetVal = URLDownloadToFile(0, "http://www.whatismyip.org", "c: mpip.txt", 0, 0)
If lngRetVal = 0 Then
fn = FreeFile
IP = Space(FileLen("c: mpip.txt"))
Open "c: mpip.txt" For Binary Access Read As fn
Get fn, , IP
Close fn
Kill "c: mpip.txt"
MsgBox "Your WAN IP is " & IP
Else
MsgBox "Error: " & lngRetVal
End If
End
End Sub
to get my IP, but how do I let clients know the IP through VB? I can't access FreeWebs, MySpace, or most other free sites out there from the main access location, so is there another way to somehow 'broadcast' or communicate the IP to any client wishing to connect?
Dynamic
morning,
im working on dynamic forms and im facing real big problems.. I am creating an application similar to visual basic.
so when i create a dynamic control (run-time), I want its properties to appear in the buttom left corner as in VB. i created MSFlexGrid and its filled according to the selected control by getting its properties from a database i filled manually.
the problem is some of vb controls are changed by comboBox and some you just fill them in ( Text Box )
for ex. cmdButton , (name) : (textbox) .. appearance : (comboBox)
is that clear?
Dynamic WBC
I want to program in a way that
Information should be displayed in Web Browser Control
And this information should come from Database ..
Can u plz guide me how to do it ....
Dynamic UDT Or Something Else?
I have a problem I am working on that involves taking pairs of X,Y,Z coordinates and joining them into sets of polylines.
Type Vertex
X as Double ‘X coordinate
Y as Double ‘Y coordinate
End Type
Type Lines
X (1) as Double ‘X coordinates of beginning X(0) and end X(1) of Line
Y (1) as Double ‘Y coordinates of beginning Y(0) and end Y(1) of Line
Z as Double ‘Constant for each line
End Type
Type PolyLine
X () as Double ‘X coordinates of beginning X(0) to end X(nV) of Line with nV Vertices
Y () as Double ‘Y coordinates of beginning Y(0) to end Y(nV) of Line with nV Vertices
Z as Double ‘Constant for each line
End Type
As you can see from the type declaration a line would be defined by its two endpoints X(0),Y(0), Z and X(1),Y(1), Z.
The problem I am having is that I will have a varying number of polylines each likely comprised of differing numbers of vertices.
I know I can create an array to handle the differing numbers of polylines.
Dim ThesePolylines() as Polyline
nP = number of polylines
ReDim ThesePolyLines(nP)
;however it appears that array sizes in User Defined types cannot be allocated dynamically. I will not know either the number of polylines nor the number of vertices in each polyline until the code runs.
Does anyone know how to create an array or other type of structure though would allow redimensioning both the number of vertices comprising each polyline and the number of polylines dymamically?
ie ReDim ThesePolyLines(nP).X(nV)
ReDim ThesePolyLines(nP).Y(nV)
About Dynamic DSN
Hai...
I have am working with CR8.5, Access2000 and VB6.0...
it is Client/Server project...
I designed.... all reports... based on one system DSN in one of the system... which... represents... D:FabricFabric.mdb...
my DSN name is "FabricDSN"... while installing my project in another network... how to proceed with the DSN...
while calling the reports... i want to display them by getting the data from required folder.... (here one thing is to be discussed... i am having... multiple databases.... in the server.... like Fabric03.mdb, Fabric04.mdb, Fabric02.mdb... so on)
while working with my project, at the starting i will select my own... firm... , i mean to which database i want to connect...
1. Now what my problem is;... I have to get my reports... by connecting... to the required database.... (since... all databases, tables, fields.... are identical)?
2. How to change the data source of the crystal report.... at runtime... and how to refresh...it at the same time...?
3. is there any way to change the data source of DSN at runtime...from vb?
I think it is clear....
thanks in advance...
Dynamic SQL
ok, I did a search that came up empty...
How do you do a dynamic SQL statement? I have an example here that just keeps adding Where statements....
so do you just AND these all together?
Dynamic Dll's?
We do exactly what you need to do, but we use ActiveX DLL's. This way you can say:
Code:
objWhatever = CreateObject("DLLName.ClassName")
I don't know enough about straight forward C++ to say this for certain, but I think you can achieve this by using a type library:
Code:
//-------------------------------------------------------------------
// IMainForm
//-------------------------------------------------------------------
[
odl,
uuid(06CFF40F-A63B-11D3-8476-00E029423382),
version(1.0),
dual,
nonextensible,
oleautomation
]
interface IMainForm : IDispatch {
[id(0x01), propget]
HRESULT Window([out, retval] IWindow** retval);
};
In the simple example above, I think that the use of the word "oleautomation" makes this class creatable with VB's "CreateObject" function.
Hope this helps.
Shrog
Dynamic Add ?
Hi,
How can i add/rmove a control to a form dynamcally?
Can add Form too?
Can i copy a form with all the controls to another form dynmancally?
Thanks
Dynamic SQL
Hi ,
I want to execute a SQL statement depending on certain parameters.
E.g.
DECLARE @NO INT
IF @CITY='X'
SET @NO = ' Select country_code from MAP where COUNTRY= 123'
ELSE IF @CITY='Y '
SET @NO = ' Select country_code from MAP where COUNTRY= 456'
here my Select country_code from MAP where COUNTRY= statement is same , so i want to form a dynami SQL for that purpose
e. g.
declare @SQLString nvarchar(100)
SET @SQLString = ' Select country_code from MAP where COUNTRY= '
IF @CITY='X'
SET @SQLString = SQLString + ' 123'
ELSE IF @CITY='Y '
SET @SQLString = @SQLString + ' 456'
How to go about it?
How to return a value from dynamic SQL?
Any help will be appriciated.
Thanks.
Dynamic PDF's
Hi
Is there a way that I can insert objects into a PDF and directly below that insert text which by the way I don't know how much text is going to be placed it varies for each form. So format will be:-
Object
Text
Object
Text
Any assistance will be great, or even to tell me its not possible
Thanks
More Dynamic
hi there , i have a data sheet , i have done so far is to copy the selected contents to another sheet then filter it and hide many columns which the user did not ask for ... my question is that how can the selection for "ONSHORE" , "OFFSHORE" and "ON & OFFSHORE" be more dynamic(these are some sort of title) as in when new rows gets added in to ONSHORE then the rows selected will be 1 row short .. is there anyway to make it pick that line up as well ??
Code:
Private Sub asd()
Range("B5:CJ178").Select
Selection.Copy
Sheets("Result").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Result").Select
Range("B5:CJ178").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=pf.Text
Selection.AutoFilter Field:=2, Criteria1:=product.Text
Range("D3").Select
End Sub
Private Sub Search_Click()
'------------------ONSHORE----------------
If pnl.Text = "ONSHORE" Then
'Range("B5:CJ778").Select
'Range("B5").Activate
'Selection.AutoFilter
'
'Selection.AutoFilter Field:=1, Criteria1:=pf.Text
'Selection.AutoFilter Field:=2, Criteria1:=product.Text
'
'Range("B179:CJ778").Select
'Range("B179").Activate
'Selection.EntireRow.Hidden = True
asd
'---------------OFFSHORE-----------------
ElseIf pnl.Text = "OFFSHORE" Then
Rows("6:178").Select
Range("B6").Activate
Selection.EntireRow.Hidden = True
Rows("179:778").Select
Range("B179").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=pf.Text
Selection.AutoFilter Field:=3, Criteria1:=product.Text
Rows("292:760").Select
Range("B292").Activate
Selection.EntireRow.Hidden = True
'------------------ON & OFFSHORE----------------
ElseIf pnl.Text = "ON & OFFSHORE" Then
Rows("6:178").Select
Range("B6").Activate
Selection.EntireRow.Hidden = True
Rows("180:291").Select
Range("B180").Activate
Selection.EntireRow.Hidden = True
Rows("292:778").Select
Range("B292").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=pf.Text
Selection.AutoFilter Field:=3, Criteria1:=product.Text
Rows("505:778").Select
Range("B505").Activate
Selection.EntireRow.Hidden = True
End If
1 more thing ... i am doing alot of column hiding for lots of combo boxes in there some sort to like shorten the huge chunk of codes ??
Code:
If mtd.Text = "Jan 2005" Then
Columns("Z:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "Feb 2005" Then
Columns("Y:Y").Select
Selection.EntireColumn.Hidden = True
Columns("AA:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "March 2005" Then
Columns("Y:Z").Select
Selection.EntireColumn.Hidden = True
Columns("AB:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "April 2005" Then
Columns("Y:AA").Select
Selection.EntireColumn.Hidden = True
Columns("AC:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "May 2005" Then
Columns("Y:AB").Select
Selection.EntireColumn.Hidden = True
Columns("AD:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "June 2005" Then
Columns("Y:AC").Select
Selection.EntireColumn.Hidden = True
Columns("AE:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "July 2005" Then
Columns("Y:AD").Select
Selection.EntireColumn.Hidden = True
Columns("AF:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "August 2005" Then
Columns("Y:AE").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "September 2005" Then
Columns("Y:AF").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "October 2005" Then
Columns("Y:AG").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "November 2005" Then
Columns("Y:AH").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
ElseIf mtd.Text = "December 2005" Then
Columns("Y:AAI").Select
Selection.EntireColumn.Hidden = True
Columns("AG:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("BK:BV").Select
Selection.EntireColumn.Hidden = True
Columns("BY:CJ").Select
Selection.EntireColumn.Hidden = True
Columns("H:S").Select
Selection.EntireColumn.Hidden = True
End If
Dynamic Desktop
I would like to display text... on the desktop dynamically. Any hints on how to do this? thanks.
Dynamic ImageList
I would like to load an imagelist from values and/or images stored in a database. I know how to load the imagelist from files on a drive but would like to have the images stored in a database so that they can be changed dynamically. Does anyone know if this is possible and if so, is there any sample code out there that might get me started?
Dynamic Forms
actually i want to ask the user how many axes you want. if he selects 2 then for both axis i want to ask the same information for each axis in each form.
so how can we make this thing dynamic..like if he want 6 axis. then for all 6 axis same form information (user input should display)....how can we do it?
in short.. same form information ....per axis...(user input)
Dynamic GUI (Need Opinions Please)
I have an OO design question. I guess I am looking for someone who has tried "Dynamic GUIs" to tell me about their experiences and opinions. What I mean by "Dynamic GUI" is that components that provide significant functionality do not exist at design time.
Imagine, the goal of the program is to allow the user to create a bunch of objects (as many as he/she wants), manipulate them in some way, compare the results, and leave.
Also, it is a stand alone application - not client/server.
*********************Design**************************
Imagine a form with a button whose label is: "add object". The button's click event procedure:
1. adds a button to the form and
2. adds an object to a collection.
*************End Design************************
Q1: Where should the collection have been declared - in the form or in some class whose "job" it is to hold that collection of objects?
Q2: The click event procedure is a form module procedure. In my design, form module procedure(s) do both 1. and 2. above - am I allowing the form module to do more work than it should be doing?
Q3: I know the form module is really a class module that provides the chance to instantiate form objects. What should the role of a form object be in a well designed OO application? Does my design too "tightly couple" the GUI and the object it works on?
I have received feedback implying this was a bad design decision for my application. What I am wondering is whether or not this design is defendable and if not how it could be made better?
Thank you very much. I would really welcome and appreciate all opinions - I realize there may not be a precise answer.
Dynamic Controls
hello
i have been trying to make dynamic buttons using vb6 using the following code
dim b as new commandbutton
but after writing the word "new" the dropdown box doesnt show the commandbutton.
i dont know of any other way.
anyone know?
DLL For A Dynamic MsgBox
I needed something like this for a client, so I created this MsgBox.
I 'm sure a lot of you have done the same.
The DLL is 52K ..... Zipped code=23K
It works with the same rules as a VB MsgBox.
- All parameters are optional
- Prompt (The text is inside a locked Text Box so the user may select and Copy it.
- Buttons (with List Constants) or you can use VB constants. ie vbOKOnly or OKOnly.
- Title (If left blank the dialog box will not have a title bar and will not be moveable)
- Context (with List Constants, Default= None) ie bsCritical
- Beep (True or False,Default=True)
- Back Color ( Color of the Dialog Box,Default=Dark Blue)
- Fore Color ( Text color,Default=White)Font=Arial 8 Bold
- Height ( Dialog Box hieght, Default=2600 Twips) Minimum=2600, Max is 1000 < Screen Hieght
( unlike the VB version which can be off your screen)
- Center Text (true or False,Default=Left justify)
Return from users' Button Click is the same as VB constants ( vbMsgBoxResult )
The client process will pause until the user clicks a button.
Esc only works with vbOKOnly and if there is a Cancel button
No Esc allowed for something like vbAbortCancelRetry (same as VB)
The Default button is the one with an optomistic context.(so the user can hit Enter instead of a button)
Feel free to use it or even modify it.
Your comments are welcome
Open project with "BassicMessage.vbp"
Download from the next post
Dynamic Commands
heya's. in the MUD i'm writing, i'd like to work in the command system dynamically... as in not hardcoding them into the proggie.
this way, users can make macro's and i can add/remove commands with ease.
but i'm struggling to come up with a way to do it. at the moment i'm thinking an ini file which would be loaded into memory. but i don't know if that's the best way to do it.
at the moment i'm using a big 'select case' statement in the code. probably not the best way.
anyone have any ideas? thanks
Dynamic Global ?
is there such a thing?
I need to create a global at runtime based on the input of a form.textfield. It will be the sales tax for the state the customer is in. It won't change. Until the workbook is closed and a new workbook with my code is opened. I've tried this:
Code:
Global Const SalesTax
At the module level. But it requires a value. Is it possible to accomplish what I'm looking for?
Dynamic Control Help Please
First of all, Thank God I found this forum I was wasting all my time in this other forum for Databases and could find nothing on dynamic controls. This forum provided tons of answers, however it also created some questions haha
This is one of the posts that I read about dynamic controls
You can do this with the CreateControl Method (look it up in VBA Help file), but you would need to have code to close the form, reopen in design mode, and then create the control, save, and then reopen the form.
It would have to be done from a Module or Form other than the one you want the controls created in and additionally you would have to destroy the controls before you recreated them each time the the function was run.
The better solution in Access is to create generic controls in the form ahead of time (more than you anticipate using), with the visible property = False, and using a control array, loop through the controls assigning properties from your table/recordset. I've done this before and it works out pretty well.
unfortunately, I am working with access 2000, so I don`t have the convenient me.controls.add method.
My questions are...
How do you reopen a form in design mode? In the DoCmd.OpenForm function does acHidden also mean design mode? Also, how would I destroy the object each time? Do I use DoCmd.DestroyObject?
Finally, according to this post, the better solution is to just create excess controls in advance... This seems really wasteful of memory or resources especially since I`ll need up to maybe 800 controls on the form... Am I right? Or does it not matter?
Please someone answer! haha tired of not getting responses to posts
Thanks!
Danny
Dynamic Polys?
I just made a game using API calls to set a polygonal region and fill it with color. now i want to do this in another game, this one in DX7. I couldnt find any DX7 toll to make polys, but i hear that the API will still work. Is this correct?
Secondly, in my previous game i could not change the number of vertices. I couldnt redimension the number of vertices. I think it said somehting like "requires constant value". Where does my fault lie? Sample code is great.
Thanks, Drew
P.S. both games are 2D
Dynamic Toolbar
Hi guys,
im currently writing a program in VBA to read in values from a text file that would correspond to buttons on a floating toolbar and then create the afformentioned toolbar. Thing is... i cant find any information about the toolbars anywhere.
Ive managed to create a new button and set the caption through vba but it is always in 'default mode' (N.B. no text or picture is displayed on the button).
A feature of the text file would be the possibility of buttons within drop-down menu buttons also... however i can't find any information on how to define the button's location on the toolbar..
SOOOO I need:
- How to alter button from 'default mode' to 'text only'
- How to specify where on the toolbar a button goes.
Dynamic Loop??
hi,
i am not too hot with programming so i wonder if you can help. i am setting up a spreadsheet in excel to monitor my self-employed hours week by week throughout each month. so i keep a record any time i work on the job name, time started, time finished, cost. i have each day of the month setup in a different worksheet (so there are 31 worksheets) and what id like to do is at any given point, submit the job name and cost to an invoice in the spreadsheet. ive created an invoice template, and using a button i can get one line of information into it but what i cant do easily is check to see which free cell is next to put the info into. so if the last entry was in row 15 of the invoice, how can i tell the spreadsheet to check from row 1 of the invoice for the next available free cell and put the information into it?
i have looked at loops and things but i cant get them to stop. they will put info in, but it ends up being into every cell declared in the loop.
the only way i can figure is to fo it with if then and else statements but this is going to be tooooooo big in script..........
Code:
If CheckBox2.Value = True Then
If Worksheets(ino).Cells(15, 2) = "" Then
Worksheets(ino).Cells(15, 2) = [a10]
Worksheets(ino).Cells(15, 8) = [d14]
Else
If Worksheets(ino).Cells(17, 2) = "" Then
Worksheets(ino).Cells(17, 2) = [a10]
Worksheets(ino).Cells(17, 8) = [d14]
Else
If Worksheets(ino).Cells(19, 2) = "" Then
Worksheets(ino).Cells(19, 2) = [a10]
Worksheets(ino).Cells(19, 8) = [d14]
Else
If Worksheets(ino).Cells(21, 2) = "" Then
Worksheets(ino).Cells(21, 2) = [a10]
Worksheets(ino).Cells(21, 8) = [d14]
Else
If Worksheets(ino).Cells(23, 2) = "" Then
Worksheets(ino).Cells(23, 2) = [a10]
Worksheets(ino).Cells(23, 8) = [d14]
Else
If Worksheets(ino).Cells(25, 2) = "" Then
Worksheets(ino).Cells(25, 2) = [a10]
Worksheets(ino).Cells(25, 8) = [d14]
Else
If Worksheets(ino).Cells(27, 2) = "" Then
Worksheets(ino).Cells(27, 2) = [a10]
Worksheets(ino).Cells(27, 8) = [d14]
Else
If Worksheets(ino).Cells(29, 2) = "" Then
Worksheets(ino).Cells(29, 2) = [a10]
Worksheets(ino).Cells(29, 8) = [d14]
Else
If Worksheets(ino).Cells(31, 2) = "" Then
Worksheets(ino).Cells(31, 2) = [a10]
Worksheets(ino).Cells(31, 8) = [d14]
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
i think there must be a more economical way to do this. id appreciate any feedback you can offer.
thanks muchly
david
Dynamic Combobox
Hey,
Any ideas on how to fill a combobox in excel vba? I have a userform setup and have a range defined as "Date Range" I would like to sill the box with this range each time it is loaded up.
Thanks,
Mike
Dynamic Reports
I have a crosstab query that is dynamically changed depending on a users choice on a form. This means the column and row headings change dynamically; i.e. one category available is a list of months, another is a list of geographic locations. My problem is how to generate a report from this one query which will display the correct data and give the correct row and column headings.
Using the Access Report Wizard to generate a report asks for the specific row and column names, which obviously change. Has anyone overcome this problem before who could help ?
Thanks in advance
Steve
Dynamic Graphs
Is it possible to generate a graph directly from VBA from a resultset ? The reason I ask is I can't seem to find a way to generate a graph from the Access graphing tools from a resultset that is generated from user form input. Maybe this question is a bit basic but as I'm a VBA beginner I'm really not sure
Dynamic Arrays
Hello,
I am wondering if someone could help me with a question. I am using VBA to count total rows in a spreadsheet, and upon find the MAX number, returning the last row's data. The dilemma is, however, that this data source will continually grow in size. The static method is OK if you always know the range, but it later dawned on me that this is a test source only; production data will be CONSIDERABLY larger. I am toying with the idea of using a dynamic array to do this. I am pretty new to VBA and thought I'd ask for some input. The code below is pretty self explanatory. I am using relative references to do this below.
Appreciate any help you can provide!
__________________________________________________
owb.Worksheets("tester").Select
With ActiveSheet.Range("A1:H500")
.Select
Worksheets.Add
Worksheets("Sheet1").Range("A1").Cells.Value = "Number of rows from A"
Worksheets("sheet1").Range("B1").Cells.Value = "=COUNT(tester!R[1]C[-1]:R[500]C[-1])"
Worksheets("Sheet1").Range("A2").Cells.Value = "Number of rows from B"
Worksheets("sheet1").Range("B2").Cells.Value = "=COUNT(tester!RC:R[499]C)"
Worksheets("Sheet1").Range("A3").Cells.Value = "Number of rows from C"
Worksheets("sheet1").Range("B3").Cells.Value = "=COUNT(tester!R[-1]C[1]:R[498]C[1])"
Worksheets("Sheet1").Range("A4").Cells.Value = "Number of rows from D"
Worksheets("sheet1").Range("B4").Cells.Value = "=COUNT(tester!R[-2]C[2]:R[497]C[2])"
Worksheets("Sheet1").Range("A5").Cells.Value = "Number of rows from E"
Worksheets("sheet1").Range("B5").Cells.Value = "=COUNT(tester!R[-3]C[3]:R[496]C[3])"
Worksheets("Sheet1").Range("A6").Cells.Value = "Number of rows from F"
Worksheets("sheet1").Range("B6").Cells.Value = "=COUNT(tester!R[-4]C[4]:R[495]C[4])"
Worksheets("Sheet1").Range("A7").Cells.Value = "Number of rows from G"
Worksheets("sheet1").Range("B7").Cells.Value = "=COUNT(tester!R[-5]C[5]:R[494]C[5])"
Worksheets("Sheet1").Range("A8").Cells.Value = "Number of rows from H"
Worksheets("sheet1").Range("B8").Cells.Value = "=COUNT(tester!R[-6]C[6]:R[493]C[6])"
'Worksheets("Sheet1").Range("A9").rows.value
'Worksheets("sheet1").Range("B9").Cells.Value = "=Max(R[-7]C:R[-2]C)"
Dynamic Functions
Hello,
I am new to VBA. Coming from Java. I have been asked to automate a test status dashboard in excel that shows testing stats ( passed, failed, percent complete ). Each test spec has a sheet that shows this information. Currently, the test manager has to manually input the path of each specification that is under the project folder and then copy a formula to reference each test spec name and the address that stores the needed data. Here's how the columns look.
Test Spec Name|Failed|Not Run|Passed|Total
Here is the formula that gets the failed value:
Code:
=+'C:Test Specification.xls]Test Case Execution Progress'!$G$4
So far i have tweaked some code I found online to add a button to the sheet that allows the user to select the folder where the test specs are stored. The code puts all of *.xls file names in the specified folder under the Test Spec Name column as a link. Here's the code:
Code:
Sub SrchForFiles()
'Searches the selected folders for Test Specification excel files
Dim i As Long, z As Long, Rw As Long
Dim ws As Worksheet
Dim FDir As String, FName As String, FPath As String
Application.ScreenUpdating = False
'**********************************************************************
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select your project's Test Specification folder directory"
.Show
If .SelectedItems.Count <= 0 Then
Exit Sub
Else
FDir = .SelectedItems(1)
End If
Sheet1.Range("B7") = FDir
End With
'**********************************************************************
With Application.FileSearch
.NewSearch
.LookIn = FDir
.SearchSubFolders = False
.Filename = "*.xls"
Set ws = ThisWorkbook.Sheets("TD Data")
On Error GoTo 1
On Error GoTo 0
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
FName = .FoundFiles(i)
'Get file path from file name
FPath = Left(FName, Len(FName) - Len(Split(FName, "")(UBound(Split(FName, "")))) - 1)
If Left$(FName, 1) = Left$(FDir, 1) Then
z = z + 1
If CBool(Len(Dir(FName))) Then
ws.Cells(z + 1, 2).Resize(, 1) = Array(Dir(FName))
ws.Hyperlinks.Add Anchor:=ws.Cells(z + 1, 2), Address:=.FoundFiles(i)
End If
End If
Next i
Else
MsgBox "No Test Specifications were found in this directory."
End If
End With
ActiveWindow.DisplayHeadings = True
Application.ScreenUpdating = True
Exit Sub
1: Application.DisplayAlerts = False
Application.DisplayAlerts = True
End Sub
So now what I need to find out how the make the code to evaluate the file name under the 'Test Spec Name' column and get the data thats needed.
Its been hard to find out when methods and fields are available for the VB objects. I haven't been able to find a good comprehensive API as with Java. However, I persevere. Can someone point me in the direction of how I should go about doing this? Also, is there an online API that show the Object methods and fields and function parameters, like with the Java API? Thank you very much
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Dynamic Tables
Hi,
I have to create a table which has more then one row - data from a database.
I then have to append a table underneath the first table.
I have created a named range in a hidden worksheet, which holds formatted headers, and copie them into the sheet as neccessary.
However, after copying the firts table, and then the recordset, I try and move down to the next cell using
Range("A" & recordset.rowCount) but it says the object is now null.
Can someone please give an exaple of copying a recordest to a range value and then writing "Hello" in the range directly below - I preseum offset is used
Thanks
Dynamic Sorting
Without much luck, I've been working with a means to duplicate the sort form in Excel. I created a form with three combo boxes (cboSort1, cboSort2 and cboSort3) three option buttons (btnDesc1, btnDesc2 and btnDesc3) and four command buttons for Sort, Clear, Print and Close Form. However, I am really struggling with the code to make the sort work.
I tried putting code under the sort button to determine the number of keys to sort by. The code looks like this:
Code:
Dim intCounter As Integer
Dim strOrder As String
Dim strKey As Variant
Dim srng As Range
Dim strKey2 As Variant
'Dim srng2 As String
Set srng = Range("C9:N65")
srtSheet = "Summary"
For intCounter = 1 To 1
If ("cboSort" & intCounter) <> "" Then
strKey = ("cboSort" & intCounter)
strKey2 = strKey.Value
If (btnDesc & intCounter & ".Value") = True Then
strOrder = "Order" & intCounter & ":=xlDescending"
Else: strOrder = "Order" & intCounter & ":xlAscending"
End If
End If
Next
If strKey <> "" Then
With Sheet = (srtSheet)
....
Bottom line...I just cant figure it out. The range is constant, however the sort Key and Order can change? Does anyone have any ideas or has anyone written code to create this form?
Thanks
Dynamic Array Fun
Hi
Im trying to write a sub that creates an array named monday if the value in one column of boolean results is true. The trouble is I don't know how entries i'll have. Here is the code I tried that gets a "Sub script out of range error on the line with the redim statement.
Code:
Sub Monday()
Dim rc As Long, Mon() As Variant
Dim rng As Range, cell As Range
Dim i As Long
rc = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range(Cells(4, 2), Cells(rc, 2))
For Each cell In rng
If cell.Value = True Then
ReDim Preserve Mon(LBound(Mon) + 1, 18)
Mon(LBound(Mon) + 1, 1) = cell.Offset(0, -1).Value
For i = 2 To 18
Mon(LBound(Mon) + 1, i) = cell.Offset(0, i + 6)
Next i
End If
Next cell
End Sub
Dynamic Charting
I am creating a list of data in two columns. 1's (in the first column) and 0's in the second column. This list is increasing and decreasing while I run a plug in for excel. I would like to create a bar chart that dynamically moves along with these changes, so that as the range increases so does the chart. Anyone got any suggestions??????
The end product would look like clock form like this
Code:
_________
|_____________
then the range would change and it would look like this:
Code:
______________
|____________
and so on. I imagine I can do it with a line graph but how can I make excell change as the range increase/decreases?
Please Help?
Dynamic Array Fun
Hi,
I'm havin issues getting an array to work. I need to use an array because i think it will make some projects I have easier. The issue is I don't really know how. In this example I have a worksheet that two columns of information I want to add to an array.
Here is the code I'm tying:
Code:
Sub ARRAY2D()
Dim Arr() As Variant
Dim rw As Single
Dim cw As Single
Dim i As Single
Dim j As Single
If Cells(Rows.Count, 1).End(xlUp).Row > Cells(Rows.Count, 2).End(xlUp).Row Then
rw = Cells(Rows.Count, 1).End(xlUp).Row
Else
Cells(Rows.Count, 2).End(xlUp).Row
End If
If Cells(1, Columns.Count).End(xlToLeft).Column > Cells(1, Columns.Count).End(xlToLeft).Column Then
cw = Cells(1, Columns.Count).End(xlToLeft).Column
Else
cw = Cells(2, Columns.Count).End(xlToLeft).Column
End If
For i = 1 To rw
For j = 1 To cw
ReDim Preserve Arr(1 To i, 1 To j)
Arr(i, j) = Cells(i, j).Value
Next j
Next i
MsgBox WorksheetFunction.CountA(Arr)
End Sub
When I run it the Redim line is hightlighted and I get the subscript out of range message.
If I take out the "Preserve" my message box in the in returns a big fat zero, which is not right
Help?
Dynamic Excel In VB App
Is there a way that anyone can think of to take a calculated value in excel and paste a live link into a VB Text box? Any help would be much appreciated.
Thanks,
MJ
UDT And Dynamic Array
I want to set up a UDT that would look something like:
Quad(1 to 4).Wall(1 to 5).Points(0 to n).X
Quad(1 to 4).Wall(1 to 5).Points(0 to n).Y
Quad(1 to 4).Wall(1 to 5).Points(0 to n).Z
where n = 10 when .Wall(1)
where n = 13 when .Wall(2)
where n = 15 when .Wall(3)
where n = 13 when .Wall(4)
where n = 9 when .Wall(5)
Is this possible? What would the Type statements look like?
Dynamic Forms?
Hi,
Not sure if this has been asked before, so sorry if it has.
Is it possible to open a form, if you know the name.
I'm trying to code a dynamic form, which relies on some input (database),
And because I would like most of the buttons not exist at design time,
it makes it hard to code it when they are based off a database input,
is there anyway to open a form, if you have say the form's name as a string?
Or am I just trying to do something which isn't possible in Visual Basic 6.
Thanks inadvance,
~ Lakie
Dynamic Report
May I know how to link the dynamic report or data report which I had added to Designers to vb6? Something like this? Pls help! Thank You!
eg.
Private Sub report_Click()
datarep.Show
End Sub
Dynamic Forms In Vb6
Hi,
I need to create a new form at runtime. I dont want to create it from an existing form, but an entirely blank form that i can populate.
I've searched hi and low about creating runtime controlls, but cannot get it to work for a form.
All help/code is welcome and needed
thank you in advance
kurt
Dynamic ComboBox
I have two comboBox, the first one is the main category,
some of category may have sub-category which display in comboBox2.
When I select category1 and chosen a related sub-category,
the sub-category is still of comboBox2 after I change main Category to category2 which doesn't have any sub_category?
SO, HOW to de-select a comboBox??
Thank you!!
|
TRACKER
