I'd like to use DLookup, DCount etc. on a SQL string held within the VBA code itself. Something like:
stSQL = "SELECT * From Contacts"
MsgBox DCount("Contact_ID", stSQL, "Deceased = False")
I'm trying to avoid creating lots of queries.
I am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code:
additionalColumns = Array("a","b","c","d","e","f")
'add additonal columns to report
For i = LBound(additionalColumns) To UBound(additionalColumns)
MsgBox additionalColumns(i)
If additionalColumns(i).Value = "a" OR "b" Then
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10)
tb1.Fields.Append FieldName
Else
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150)
tb1.Fields.Append FieldName
End If
Next i
I am using Windows 8.1 64 bit system. Into attached access file (OfferStudy.accdb) ,trying to load an activeX TreeView control by defining into a form module. First, I created an empty form and activeX Treeview control.When I define tv variable As TreeView as created in application, it is changing as Dim tv As treeView instead of TreeView. After my code is completed,an error message ("user-defined type not defined") is returning as shown in attached (DebugError.png)file.
I suppose that there is some conflict on creating activeX control on my Access 2013 application.
I am trying to make an automation in order generate reports based on query ( person name,person email address ) , export them to a folder in PDF and then send them one by one to each person email address.
What is happening:
- the reports are generated and exported fine
- the email are sent to the right addresses but the first person receives the correct report , the second person receives the report from the first person plus its own report and so on.
Here is my code :
Private Sub MakeReportSendEmail_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
[Code] ....
I is it possible to generate a bar-code that which is a path to a file name on my server from access 2010? if so here is what i would like to do:
- for each expense i enter in my application i'd like to generate and print a bar-code
- scan bar-code on copy machine (which is path and file name of document to be scanned)
- scan my expense receipt (copy machine now knows where to send the receipt and how to name the file ala the bar-code that was scanned)
I'm not sure if this is at all possible using only access and vba.
The following is code to open a form and records with a specific date as input by the user:
Private Sub cmdDisplayCovers_Click()
On Error GoTo Err_cmdDisplayCovers_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmInventory"
stLinkCriteria = "[tblCovers].[Date] = [Forms]![frmCoversByDate]![txtDate]"
DoCmd.OpenForm stDocName, , , stLinkCriteria
etc. etc.
generate a message to user if no records match the input and then return to the input form. Currently, the form opens even with no records.
I have a database studentreview. with the name of the student, the location of the student the course and the status of the course. What I am trying to do is when a condition is meet the name of the course "Anatomy" is meet and the status "Done" is meet I want to auto-generate a new record with the same student name with the same town and the course will be "anatomy 2" and status "not done". The name of the student and the location are linked tables with the studentreview table. This is what is did with a record set but clearly I am doing something wrong since nothing is happening
Public Sub AddRecordset()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblStudentreview")
[Code] ....
I am required to write a code that will allow me to generate a table at the click of a button. Is there such a thing?
The table name should be Table1, and it contains 7 columns.
First column: Auto number (PK)
Field1: Memo
Field2: Memo
Field3: Memo
Field4: Memo
Field5: Memo
Field6: Memo
I am using a API to generate a random / unique directory. This an API form a 32-bits environment.
I know that you can still use it when declaring it with PtrSafe
This is the code I am using:
Option Compare Database
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
Private Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" (ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As Long, ByVal lpTempFileName As String) As Long
[Code] .....
Is it possible to generate a report from 2 listboxes?
I have attached a pic of what I am trying to accomplish. I am wanting to have a report open with only the criteria that a user selects from the listboxes.
listbox 1: User selects a case number(s)
listbox 2: User selects the fields they would like in the report
So, if the user selects:
Case number: 13-001
Then selects fields:
Allegation
Incident date
Focus_Last name
This report would be:
Code:
Case Number Allegation Incident Date Focus_Last Name
13-001 DUI 06/01/2013 Doe
I generally use Cognos for my queries, so the overly user friendlyness of access is confusing me and im not proficient enough in sql to just go in and edit the code...
How do i generate a query that groups data in unique ways in the result, but does not change the data in the originating table.
For example:
I have the column tbemployment.dept . In a crosstab query i want everybody that isnt tbvoter.dept='BELLS' to be counted as 'other'.
In Cognos i would do this in the 'data definition' window for that field and use 'if (tbvoter.dept<> 'BELLS') then ('other') else (tbvoter.dept)'
How do i do this in access?
I am feeling around my database which was upgraded from Access 2003 to Access 2013. I am getting error messages and I was trying to figure out why a particular query gave an error. I clicked on "data definition" and I think it opened a new query, but it will not let me close it. The message is invalid SQL statement, expected Delete, Insert, Procedure, Select or Update. There is no way to cancel this.
I had to use the task manager to close the database, not what I like to do.
What do you do with "data definition"?
I have a table with a field that contains IDs, e.g.
123
456
789
I would like to generate a single string from this table, seperated by commas, e.g. 123, 456, 789 and output to a field in an existing table. This will then be used in a SQL statement.I am new to VBA and don't reallly know where to start/
I am creating a database for my school and I can't create a relationship between two tables. There are only two tables and I need them to be one-to-many however it says there is an 'invalid field definition'. I have attached the database.
View 3 Replies View RelatedHow to generate an export specification file by using Access 2007. Many instructions says click on "Advance" while exporting a text file manually but unfortunately i can not find that "Advanced" option in my access software.
View 5 Replies View Relatedi have these code to auto generate member number but the highlighted line generate error 94 - invalid use of NULL:
Private Sub cmdGetNumb_Click()
'On click of button a new Member Number is generated and
'focus is moved to tFName field.
Me![nMembNum] = NewMembNum()
Me![tFName].SetFocus
'Prevent accidental click
Me![cmdGetNumb].Enabled = False
[Code] ....
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
Is it possible to create a button that automatically saves a report as .pdf? And automatically generates the filename.
Like 0810-544 (0810 is the date and 544 is the ID from the form.)
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
I'm using VBA code to open a query, generate a report for a test and an answersheet. I'm using the same report, and don't want to make many reports. I have the below code which runs my query, and generates the reports.
Private Sub Command2_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "1", acViewNormal, acEdit
DoCmd.OpenReport "WrittenExam", acViewPreview, "", "", acNormal
DoCmd.OpenReport "WrittenExamAnswerSheet", acViewPreview, "", "", acNormal
End Sub
I have a need to be able to set the report title for both reports when running my code.
So Access was unable to manage the amount of content that I needed to produce in a single report. The reports contain 12 charts/graphs all of which I needed on separate pages (one chart per page). As such I could not create large enough charts in access to fit to each page, as Access limits the amount of space one can use in a report.
I downloaded Microsoft Reports Builder 3.0 and built my reports as needed and can generate them as desired at current. I'm wondering if there a way to call that RDL file I created to generate my reports from a form control button in Access?
In this thread:
http://www.access-programmers.co.uk/forums/showthread.php?p=549287#post549287
I had a problem. Now I decided to just make two tables, one with sales for a "passer by" and/or directly to a client without a case/labour costs. And a table for sales in relation to a case with labour costs etc. I guess I still m*ssed up on that part and have to make do with that decision (kind of like I depicted in the photoshopped image).
For invoicenumbers I want to use DDMMYYYYHHMM. When adding that field to the design of a table, how do I go about that? Do I just use a Text field and create an input mask for that? Or do I need to use a number format with a certain input mask? Or would it be wiser just to use the date/time type of field and give it a new inputmask? I guess 000000000000 would be the one for that, right? Although I read the help file I do not know if I should use ;0; behind those first group of zero's or not. Yes it is a mandatory field, but it will fill in itself as soon as a product is typed in in the product/invoice list, with a default value of the current date and time.
I need to brush up my knowledge on the formatting for that so that 28-05-1975 19:55 will be displayed and stored as 280519751955. Or is that also done automatically by using that input mask? Or will that give an error when Date or Time() etc. is used for that field in VBA?
Just a few questions I need some help with, I've been trying some stuff but it really doesn't "stick".
And if anyone has some time, I could really use some general advice/ideas for setting up the invoicing part differently for a new version of the db yet to come in the future.
I have a query based on a query, when I added a new field to the base query, this field did not appear in the list of fields in the design definition when I try and add to the upper query. It just shows the original fields only.
Same thing happened in a field on a report - I had to delete the field and create it again...
(named: Invalid Field Definition - Unable to set up cascade update but I have done what the moderator advised and have not been able to fix a very similar problem.
I have planned a database and must adhere to the plan or change it and basically I initially said that I would like to enforce referential integrity to synchronise my tables and I would also like to cascade update and delete to save input time because changes will be automated between table (where fields are connected.).
I have attached a plan I did if that makes the purpose of my database clearer.I have attached my database and have established the relationships* without referential integrity enforced I would like between my tables but don't know how to make them one-to-many relationships nor can I enable cascade update or cascade delete or enforce referential integrity.
I am new to databases (and to this forum) and have been promised by my line manager that I won't have to specialise, I don't think I have created the relationships but they are supposed to be between Vehicle ID and Hire Number, and Customer ID and Hire Number.
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I create a query from different tables. With an website based insert statement I put the data from this query to another table.
Is it possible to fill the table without the insert statement. But automatic done by the database itself. So insert/update all the query data automatic into the table
ThanXs