Is it possible to retrieve the format of a field when looping through recordset.
e.g you can get the field type by using
Quote:
rs.Fields(y).Type
But the type for decimal or percentage is the same. It is the format that is different
I need to be able to see what is formatted as a percentage and what is formatted as fixed.
So far I had several Subforms which would open in a mainForm depending on the combo box value. My combo box values were exactly the same as subform names so that I can easily change subform.recordsource = combo.text and it would show the proper form. Now, I'm thining, instead of having all those forms, I could have only one form and just changing it's recordSource property with the sma combo box, but this time, combo.value would be the name of the SQL string that the form needs to take as it's recordSet.
What I want to know is, if this will work at all, and if it would, where do I have to set my vars...in the Form_load()?!:confused:
tnx
D
I have written code to retrieve data from a back-end, that is then bound to a form (late binding forms).
From the retrieved record set I want to update a combo box using 1 field from the data in the record set, only showing unique records in the list.
Is there a way to gain access to the Field Properties using VBA?
I'm trying to loop thru the Tables in a database and find out the Fields that are indexed in each table.
I can loop through the table Defs to get each table name and a list of Fields but I can't seems to get to the Field Properties, namely "INDEXED".
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = Nothing
I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF
Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop
rstPrice.Close ' Tidy up
Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.
Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String
[code]....
This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?
Is it possible to add/edit a record from a DAO querydef that has a calculated field in.
Code:
Set qdf = db.QueryDefs("qryOutput_" & strDept)
qdf.Parameters("Enter Date") = dDate
Set rs = qdf.OpenRecordset()
Using the rs.Edit or rs.AddNew worked fine until I put a calculated field in the query.
The calculated field is just a total of some fields in the table.
I thought this was something to do with dbOpenDynaset but it just keeps having the same error
Cannot update. Database or object is read-only.
I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
View 1 Replies View RelatedI am making a new Record in the design view of a table (creating a new field in the table), and I am assigning it a date type, is there a hotkey that will select the field properties sheet so that I can select the format of the field without having to move my mouse?
View 1 Replies View RelatedHi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.
Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.
Using Access 2007:
how to get macro properties using vba? I've attempted something similar to the below to try and divine macro properties:
Code:
Sub MacroDiscovery()
Dim mcr As AccessObject
Dim prop As AccessObjectProperty
For Each mcr In CurrentProject.AllMacros
Debug.Print mcr.Name
For Each prop In mcr.Properties
Debug.Print prop.Name
Debug.Print prop.Value
Next
Next
End Sub
Nothing comes up except the macro names. In my database, I have an AutoExec macro that I have manually added a description to; "This macro auto-executes when the database is opened by virtue of it's name." I can view the description by right-clicking on the macro and choosing 'Object Properties'.
Background:I am attempting to make a universal Access Database documenter for the many, many homemade databases on our network. I'm only interested in certain pieces of information and I would like to make a report to view the information how I want to view it. Thus, I don't care for the built-in documenter--I want to do it my way.
I'm using conditional formatting on a datasheet to change the background colour of a cell. Is is possible to retrieve that colour?
View 6 Replies View RelatedIt's been some time since I wrote the following, which takes a string made up of "Lastname Firstname Telephone" and writes it at a specific time in Outlook. It uses Late binding so it works across all Oulook versions.
Code:
Public Function funOutputAppointmentToOutlook(dtmDate As Date, strSubject As String)
Dim olApp As Object
Dim mNameSpace As Object
Const olFolderCalendar = 9
Const olAppointmentItem = 1
[Code] ....
I would like to first check the specific Outlook time slot whether the string exists already and only if it does not exist to write it.
I created and new field and made the source "Unbound" so that it is not connected to the field I copied it from. How do I now make that the information I enter in the new field does not duplicate in all of the similar fields in other entries.
Specifically, my database is a database of employees and the field Title is "Active". If I enter "Yes" by one employee entry, all employee entries will then state "Yes" by the Active field.
How can I change the properties so that by some employees I can write "Yes" and by others I can enter "No".
I am not so familiar with Access so if you can be specific I would greatly appreciate it.
I have a form called Orders.I need to lock this form once an invoice has been printed .
I have a check box on this form ,that if ticked should lock that specif order .
This is my Code:
Private Sub Form_Open(Cancel As Integer)
If Me.lockorder = True Then
Me.AllowEdits = False
End If
End Sub
The problem is it does nothing . I am using the open event of the form .The checkbox is called LockOrder.
Okay, here's the query as simplified as possible:
Month - Days - PreviousMonth
Month and Days are pulled from a table called Calendar. How do I get PreviousMonth to display the Month from the previous record?
I was looking at a ranking formula, and it seems like it compares the field to the previous one, but I am not sure how to apply it to my situation. The ranking formula I was looking at:
(Select Count(*) from Data Where [ProductA1] > [Data1].[ProductA1];)+1
Thanks in advance for the help.
I need to retrieve the count of emails in a shared exchange mailbox in Outlook 2010. I found the following software that retrieves the count I need, but only in default folders or ones that are 1 layer deep. The folder I need is two layers deep Mailbox -> Inbox -> Completed. Is there anyway to drill down just one more level?
Const olFolderInbox = 6
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNamespace.GetDefaultFolder(olFolderInbox)
[code]....
I've got a requirement to retrieve inbound email from a dedicated mailbox (probably through imap), and populate a data table with the results, including downloading any attachments and either storing them in the file system and referencing them, or attaching them to the database in an ole field, then mark the message as read.
View 2 Replies View RelatedBasically i'm trying to link Access to Outlook to retrieve emails , When using the wizard to link outlook folder to Access it fails, i've been informed that this is due to the version of Access they have installed which is a ThinApplication.
i was wondering would it be possible to access / link my outlook emails to access via VBA Coding?
All i need is the raw contents of the email and subject.
Current situation is that I have this form with a chart.
Row Source :
Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;
PolyWrongRegInsCount; <- This table is one of the queries that I created.However, the data is manually added. I want to get the data from excel spreadsheet. And load into my queries
is there is an easy way to create a table with defined field properties.
I have an excel file with over 300 column names and there field length.
I can import the name by transposing the list and then importing however the field length will have to manually edited which i don't fancy doing,
any ideas???
Field NameLength
BATCH NBR5
BATCH DATE8
BATCH CODE3
SUB PRODUCT3
FILE TYPE1
Hi All,
I have only been playing with MS Access for a little while and I do not know all there is to know yet so I figured I come here to ask a question.
I use MS Access in web design and lately I have been experiencing a problem with my registration form which inserts new users into an MS Access database table.
Lately my registration form has become a target for spammers trying to inject spam in the form of html code into the password field of the online registration form.
I figured that I could deter this from happening by only allowing the letters A - Z and numbers 0 - 9 in the field and not allowing characters like < > and ? and ".
I assume I can do this with a validation rule but I'm unsure as to how I would go about setting this up. Limiting the field size to only 50 characters does not seem to work unfortunately.
Any help on how to set up a validation rule would be appreciated.
Thanks
Mechaworx
In a simple data retrieve/update form, how I can set a text field’s properties Enabled to No based on that particular record value?
For instance, there are 4 fields, employeeid, employeename, employeetitle, employeephone
I would like to set the employeephone text field’s properties Enabled = No, if the employeetitle is Manager
Can anyone help?