Hey, is there a way to get the full list of tables in the database by using VBA? I am trying to do a backup function, where I want the database to fetch all the table names and put them into an array, to export them one by one to the backup file........hope you guys understands me.....thanks in advance
View RepliesI have a table that holds the names of locations and a count for each location. example below
LocationCount
INR100
INR16
MTU245
MTU123
INR300
RVO1234
TC45
TC188
The table is always changing and I need to create a query in code that sums the count, grouping for each location. These values will need to be stored in a dynamic array for further processing. How do I store this query value into a dynamic array.
array would need to store these values
(INR, 316)
(MTU, 368)
(RVO, 1234)
(TC, 233)
Thanks in advance
Hi all,
I have a table with multiple records per individual..
I need to search through all of their results to flag up those in a query that do not match the approved list..
what is the best way to do this?
I am trying a recordset loop but am struggling a bit...
here is my code so far
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strPscale As String
strSQL = "SELECT Eng_Speaking, Eng_speaking from qry_Sub1_crosstab_Crosstab;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Me.Text1 = ""
Do While Not rst.EOF
strPscale = rst.Fields("Eng_Speaking") & ";"
rst.MoveNext
Loop
Me.Text1 = strPscale
Set rst = Nothing
End Sub
but I need to somehow incorporate an array into this to check the score...
any ideas?
ta
John
I have built the first part of a table analyser into one of my projects as below:
It lists a complete list of tables except system tables for the db being assessed and writes all the table names to a table then the table properties, the fields plus there properties And relasionships and Indexes.
The Idea behind this is When I build a system I always use a frontBack Arrangement and versions within my management system.
there are always two Datafiles one the working datafile where the changes are made during design and the production model used by the users which is the one that will need to be updated by this tool.
The problem I'm having is I don't need to store all the field properties within the tables as a lot wont be used for updating the production model.
So I've been looking at ways I can generate a list of property names that I want stored so when my code loops through the properties list it can before writing the property name and value to the table check it against a list.
My question is would it be better to use an array or enum so I could check for an item and how could I best achieve this as I've not used enums before and I'm not that good at Arrays but there's more help there.
I have in the past used a list like: Prop1,prop2,prop3 which I then check using instr like instr("Prop1") which will return a start position so I know it's in the list but I think this is a bit messy so want to make the improvements
Any help would be much appreciated
mick
Hi, am new here and need a hand
i'm stuck on part of a program where i have a form which has two look-up lists (both looking up names of towns from the same table) and there is also a text box (called distance), which after choosing the town names from the look-up lists, should get the distance between the two towns from the array (in VBA).
thats where i have a problem, for now i have two towns (planning to have probably around 20 maybe more in the future), and something in the array isn't working
below is a link to a zip with that part of the array
i would be really grateful for any help, as i don't have a clue what went wrong
cheers
Hi, totally new on MS access. Is it possible to solve this following task in Access. (using it in Excel today)
Ex.
2d table?
Prod 1 Prod 2 Prod 3
Prod 1 0 10 20
Prod 2 10 0 30
Prod 3 20 30 0
Question 1: What did you just create?
Answer: Prod 1
Question 2: What will you create?
Answer: Prod 3
Then Access will show 30.
Probably explaning it bad but hope you understand and can answer me if this is possible to solve in Access....
The mission is to create a database with setup times between diffrent products, and make it possible to ask a question to get the answer.
/Martin
I have played around with the example from Adam, and wondered how one can make the data output avialable in a form.
I have tried the on click event on a label, but that doesn't work.
I would appreciate any pointers.
I used the 2003 version in this post :
http://www.access-programmers.co.uk/forums/showthread.php?t=142130
I was wondering if this was possible or not.
Can I write a function like the one below, which will accept a value and return many values?
I would like to set various columns from one score. If I have something like “Expr1: returnArray(42)” in the query, can the function return the 3 values and put them in separate columns?
If anyone has any ideas it would be greatly appreciated because I am re-writing my software to have no calculated values.
Public Function returnArray(score As Integer) As Variant
Dim arrMatrix(2) As String
'some calculations here to set the arrMatrix array.
arrMatrix(0) = "Joe"
arrMatrix(1) = "is"
arrMatrix(2) = "Stuck"
returnArray = arrMatrix()
End Function
Hello,
I have a question:
1) Can I use the Array as the field name of Table? For example: Table - ABC, has 3 fields: Value(1), Value(2), Value(3).
2) If not, I define the field names as follows:
Table - ABC, has 3 fields: Value1, Value2, Value3.
Can I use the following states:
For i = 1 to 3
ABC![Value & i] = 100
Next i
Appreciate your help!
Thanks in advanced!
LiBin11011
Hi,
could anyone help me please, I want to populate a combobox on a form with the values retrieved froma dynamic array.
Thanks in advance.
Hi all
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
Any idea how I can get around this??
Thanks
Damon
Hello Everyone;
Here's my goal: 1) to set up a 7 column table containing a list of 147 numbers from a recordset (StNumberRS) and 2) to compare those numbers to a separate recordset containing a partial list of those numbers (completedStores). If one of the numbers in the completedStores list matches a number on the StNumberRS list show the 'icon_yes.gif' image, otherwise show the 'icon_no.gif'.
I can loop through the StNumberRS and set up the 7 column table, but I'm stuck trying to loop through the completedStores list while inside the StNumberRS loop.
Could someone look through my code and see where I've gone wrong?
Thank you!
Code:<% Dim StoreRS, Query, AltRow, ProjectQuery, ProjectRS, CompletedStores_variable, strValue, CompletedStores_ArrayProjectQuery = "SELECT * FROM Projects WHERE Id =" & Request("Id")Set ProjectRS = Connect.Execute(ProjectQuery)CompletedStores_variable = ProjectRS("CompletedStores") CompletedStores_Array = Split(CompletedStores_variable,",")Dim StNumber_variable, StoreList_ArrayQuery = "SELECT StNumber FROM Stores ORDER BY StNumber"Set StoreRS = Connect.Execute(Query)StNumber_variable = StoreRS("StNumber") StoreList_Array = Split(StNumber_variable,",")%><table width="100%" border="0" cellspacing="0" cellpadding="10"> <tr> <td> <%If Not StoreRS.EOF Then%><!-- Display store list in a table, showing 7 stores in each row: --> <TABLE width="800" cellspacing="0" cellpadding="2"><tr><td colspan="14" align="center" bgcolor="#FFFF00" class="subtitle">PROJECT STATUS</td></tr><tr class="required"> <td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td></tr><tr bgcolor="#CCCCCC"><td colspan="14"><img src="../images/spacer.gif" width="1" height="1"></td></tr> <% Dim i, theCount, remainder theCount = 0i = 0Do While Not StoreRS.EOF' The StoreList_Array contains 147 numbersFor i = LBound(StoreList_Array) TO UBound(StoreList_Array)' The CompletedStores_Array can vary from zero to 147 elements. Each strValue is one element of the array.'For Each strValue In CompletedStores_Array' As long as theCount equals 7, create an empty row separaating the table rows...remainder = theCount Mod 7If remainder = 0 thenIf theCount <> 0 thenResponse.Write "<tr><td colspan='14'><img src='../images/spacer.gif' width='1' height='1'></td></tr>"Response.Write "<TR>"End IfEnd If'... and create a table row populated with 7 elements from the StoreRS("StNumber"). If StoreRS("StNumber") = CompletedStores_Array(i) Then Response.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_yes.gif' width='13' height='13'></TD>"Else' Output value of current CompletedStores_Array element for debuggingResponse.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_no.gif' width='13' height='13'>" & CompletedStores_Array(i) & "</TD>"End If If remainder = 6 thenResponse.Write "</TR>" & VbCrLfEnd If theCount = theCount + 1i = i + 1StoreRS.MoveNext'next next Loop ' close out the last table row if its not alreadyIf remainder = 0 thenResponse.Write "<TD colspan='12'> </TD></TR>" & VbCrLfEnd If If remainder = 1 thenResponse.Write "<TD> </TD></TR>" & VbCrLfEnd If%> </TABLE> <%Else Response.Write "<p>There currently is no data for this project.</p>"End If%> </td> </tr></table>
I've built a query that selects certain customers based on a purchase date. I now need to assign these customers to various sales reps and display the customer information to them in another form that they can alter information in.
I was going to put my sales reps id into an array and loop it through the query updating it. Let's say i have 7 sales reps working that day, I would then enter their id's into fields and have the array assign these numbers into the query Sales Rep Id field for each row.
I reviewed a few ways to rank based on calculations and use them, however what I'm looking to accomplish is subjective ranking whilst limited the ranks to the total number of records in the recordset.
e.g. you have 50 records. Manually moving Record # 30 up to #3 would bump the existing records #3 to #29 down one (to #'s 4 through 30) and records >30 would stay the same.
Would deploy this to the end users within a continuous form, each row having Up/Down arrows to move each record up/down 1 rank as well as provide a combo box so they may choose any value within the array they want to set it to...
Anyone know if Access 2007 has control arrays or some thing similar?
Thanks
Hi, I have a problem deleting a specific row and field that my query have.
What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4
what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example
Dim IntRow As Integer
Dim IntCol As Integer
Dim varData As Variant
For IntCol = 0 To numFields Step 1
For IntRow = 0 To numRows Step 1
If (rst.EOF = False) Then
'Check Field 1 for value 47-72 to delete
Else
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then
varData(IntCol, IntRow).Delete
End If
End If
Next IntRow
Next IntCol
In the example Above my syntax is not correct for :
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72)
and for varData(IntCol, IntRow).Delete.
can anyone help me to find the exact syntax i need to use to execute the program above?
THANK YOU!
I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like
BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x)
So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.
Hello,
I wanted to catch a little advice if I may be so bold. I have a HTML form with a series of checkbox values.
Code:<form method=post action=form-test.asp><input type=checkbox name=drinks value='cocal-cola'>cocal-cola<input type=checkbox name=drinks value='sprite'>sprite'<input type=checkbox name=drinks value='lucozade'>lucozade<input type=checkbox name=drinks value='oasis'>oasis<input type=submit value='Submit'></form>
Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.
Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client
However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.
Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?
Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?
Cheers,
trufla
Is it possible to send several form's OpnArg as Array ? Now I'm using a long text string including || as divider so I can split them later into Array.
View 8 Replies View RelatedI am having a problem with parsing the array value to Forms!<arraryvaluehere>.dirty.
Please see the code below:
Public strListOfForms(0 To 2) As String
strListOfForms(0) = "frmForm1"
strListOfForms(1) = "frmForm2"
strListOfForms(2) = "frmForm3"
For i = 0 To 2
Debug.Print CurrentProject.AllForms(strListOfForms(i)).IsLoaded
Debug.Print Forms!strListOfForms(i).Dirty
Next i
Note: CurrentProject.AllForms(strListOfForms(i)).IsLoade d works and Forms!strListOfForms(i).Dirty does not.
Is this because it is taking "strListOfForms(i)" literally? Is there a way that I can get around this?
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False
ReDim Preserve ArrRepOrder(j)
ReDim Preserve ArrItem(j)
[Code] .....
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code:
With CreateObject("Shell.Application").Windows
Set ieWindow = CreateObject("InternetExplorer.Application")
ieWindow.Visible = False
apiShowWindow ieWindow.hwnd, SW_MAXIMIZE
ieWindow.Navigate "Web address for server-side php file"
End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
The procedure call "Call RearrangeCoordinates(RearrangeCoords(), ReturnCoords())" return a message "Type mismatched: Array or user-defined type expected"Are there any views as to why the message is being displayed?
Private Sub CommandButton1_Click()
Dim ReturnCoords As Variant
Dim ...........................................
ArrayLength = UBound(ReturnCoords)
[code]....
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.