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
I am using MS Access with SharePoint Lists and some Access Tables to generate reports in pre-formatted MS Excel Worksheets. My main module opens several classes. One of the classes receives a worksheet to keep in a historical book. This class can receive any one of several different types of pre-formatted worksheets. After all is said an done the class adds a cover sheet to the consolidated workbook and adds hyperlinks to the other sheets, kinda like a table of content.In the class I have the following in the general declaration.
code: Public Enum BookType A = 1 B = 2 End Enum Private pBookType As BookType
Later in the class I have the following:
Code: Public Property Let LoadBookType(ByVal btNewValue As BookType) pBookType = btNewValue AddWorkBook End Property
in the main modual I have the following
Code: classConsolidated.LoadBookType = A
When assigning the above property it passes in "11"
if I change it to the following
Code: classConsolidated.LoadBookType = BookType.A
it passes in "1" as I expect and use in a Select Case statement later in the class.So why does one pass in "11" but the other passes in "1"?
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.
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
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...
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
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()
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
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...
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?
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.
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?
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
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)
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.
