Modules & VBA :: Why Isn't Enum Returning Correct Value
Aug 7, 2014
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"?
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber DeviceRecNo - Number ExcludeFromCheck - Number (1 = yes, 0 - No) StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code: Public Function test() Dim db As dao.Database Dim rs1 As dao.Recordset Set db = CurrentDb Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'") Debug.Print rs1.RecordCount End Function
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
I have the following two querydefs. Here is the SQL; qry1 SELECT [dtFind], tblData.dtReading, tblData.dblValue FROM tblData WHERE DateDiff("n",[dtReading],[dtFind]) Between 1 And CInt([intMins]);
qry2 SELECT [dtFind], tblData.dtReading, tblData.dblValue FROM tblData WHERE dtReading Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n",-1,[dtFind]);
[dtFind] and [intMins] are parameters.
I use the querydefs is VBA code as such Dim db As DAO.Database Dim rstDataSQL as DAO.Recordset Dim qdfData As DAO.QueryDef Dim strQdef As String
Set db = CurrentDb() strQdef = "qry1" ‘or qry2 Set qdfData = db.QueryDefs(strQdef)
‘Set values of parameters qdfData![dtFind] = dtDate qdfData![intMins] = intMins
Set rstDataSQL = qdfData.OpenRecordset
qry2 executes significantly faster than qry1, but I am having issues getting the correct results.
If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.
However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will. :mad:
I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.
The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.
I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.
Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.
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
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
I'm trying to pass some dates from an excel userform into access.
The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field in access in the American format.
In access the date fields are set to Short Date and the example shown for this format is in the UK format. I assign the date to a variable before passing that variable to the update SQL string:
Code: s1 = Nz(DTPicker1.Value, #1/1/2000#)
I have dimmed s1 as date and then added:
Code:
s1 = Format(Date, "dd/MM/yyyy")
My update string is:
Code: "SET [Stage 1] = " & "#" & s1 & "#" & " "
I suspect that the nozero function may be the issue but am at a bit of loss atm.
I have this report that needs to be export - 1 report per record
The report exports out but its the same record export out each time ( the name changes - but the data doesn't)
Code:
Private Sub Command0_Click() Dim MyDb As DAO.Database Dim rsemail As DAO.Recordset Dim RefernceNumber As String Dim FilenameZ As String Set MyDb = CurrentDb()
I am trying to load a form based on an if statement. I think my issue is that I have the DB set to Display form "frmSplash" on open. I have tried the following (frmSplash form load event) but it continues past the frmMenu and stops at the frmSplash. I want to open the DB and look to see if it is registered and if yes then open frmMenu. There is 1 record in tblRegistration so it should open to frmMenu. I checked and it is seeing the 1 record.
Code: Private Sub Form_Load() Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblRegistration]") If rs.RecordCount > 0 Then
[code]...
Does the display form on open override all? how to achieve to goal?
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.
I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.
Code: Private Sub Command68_Click() 'SEARCH AND FILL COMBO BOX On Error GoTo errTrap DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM tempList;"
I have this code that works. I have a form with 3 tabs. When I select the middle tab I am prompted for password but if I click the 3rd tab I am not.
Code: Private Sub TabCtl9_Change() Dim strInput As String Dim ctl As Control ' Hide controls on tab until correct password is entered For Each ctl In Controls
Staff are monitored to make sure they are keeping up to date with our customers. A customer can have multiple projects going through the factory at any one time. Each customer has a record per project and a 'general' record. Ideally we would like our staff to be able to move the 'general' record when they update a project record as opposed to either having to find and then update the general record after, or forgetting and calling the customer again 2 days later!
Including a msgbox for the EnqNum seems to show the general record correctly, however being new to access I am unsure if I have the update part correct.
Code: If Me.chkMoveGen.Value = "-1" Then Dim EnqNum As Integer EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13") DoCmd.RunSQL "UPDATE tblEnquiries " & _ " SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _ " WHERE e_id= EnqNum"
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed. 2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code: Private Sub btnJobEntry_Click() 'GOAL: open the work order form to the correct entry 'METHOD: store the uniqueID to a variable, then use that in the open command's where clause Dim strJobID As String 'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code: Dim strDate As String Dim strWOStatus As String Dim CurrentConnection As ADODB.Connection Dim adoCMD As ADODB.Command Dim adoRS As ADODB.Recordset Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error: Error: 424 Description: Object Required
The line of code it errors on is:
Code: Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
Code: Public Sub test() Dim frm As Form Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I have just added a function to a database to strip out any commas or quotation marks from a passed string.
However, it is returning a black string even though when I step through the function it creates the out string correctly and the variable "OutString" is populated when the Exit Function command is executed.
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String Dim i As Long If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:
Code: 'UPSData Class Module Private p_LetterArray() As String Private p_date() As Date Private p_LetterArraySize As Integer
My Properties
Code: 'Properties Public Property Get LetterArray(index As Integer) As String LetterArray(index) = p_LetterArray(index) End Property Public Property Let LetterArray(index As Integer, NewValue As String)
[Code] ....
As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.
When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:
Get LetterArray is called. p_LetterArray(0) does equal "src" Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""
Same steps happen with the same results ("12:00:00AM" vice "")
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user 2) Search that Part Number into Table1 3) Take returned Parent Values associated with Part Number and store in Array 4) Modify Array values 5) Search Array values using a For Each loop into the query 6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query. 7) Also return the total rows in a specific column (Order Numbers)
It is basically a DCount, and it should find records, but returns 0 all the time. My code is:
Code:
Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn Dim timeformat As String timeformat = "#mm/dd/yyyy hh:nn:ss#" 'need this, to convert it to US datetime format cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")") End Function
sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code: Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant Dim priSubID As Long Dim priLocusID As Long Dim priResult As Variant priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
