Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Microsoft Access

217K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

No need to check if the form is idle.  I have a point of sale program written in Access and when a sales transaction is finished a form pops up to ask if a receipt should be printed.  The problem is the sales clerks walk away instead of hitting the "No" button, leaving the form sitting there for the next clerk to step up.  

I just want the form to close after a minute if the clerk forgets to click the "No" button.

Thanks!
0
Enroll in October's Free Course of the Month
LVL 10
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Is there a way Ms Access can capture data through scan, for example if an invoice is coded expense then in the field for cost type it should write an expense.
I think there is an opportunity to do away with routine bookkeeping by using VBA code as long as MS access can read the scan, I can for see non-accountants doing complete booking keeping & consolidation of accounting statements on their own without knowing what is in the background. I think the only cost here is buying a scanner.

Regards

Chris
0
Hi every one,
I have this code below is working fine for backing up the file to a new file name
is it possible to make it only backing up only All tables to the new file name.

Function BackupSource()
    DoCmd.Hourglass True
    On Error GoTo Errorhandler
Dim strBu As String
   Dim buf As String
      Dim MD_Date As Variant
         Dim fs As Object
            Dim strSourceName As String
               Dim strSourceFile As String
                  Const conPATH_FILE_ACCESS_ERROR = 75

strSourceName = CurrentProject.Name
strSourceFile = CurrentProject.Path
buf = "C:\WinRef\"

   If GetAttr(buf) <> vbDirectory Then
   MkDir buf
   End If
   
MD_Date = "HCS" & Format(Date, "yyyy-mm-dd-") & Format(Time, "hh-mm-ss")
strSourceFile = CurrentProject.Path

strBu = "C:\WinRef\" & MD_Date & "\"
    MkDir (strBu)
         
            Set fs = CreateObject("Scripting.FileSystemObject")
               fs.CopyFile strSourceFile & "\" & strSourceName, strBu
            Set fs = Nothing

DoCmd.Hourglass False
    Exit Function
   
Errorhandler:
    DoCmd.Hourglass False
    DoCmd.OpenForm "MsgE_BkUpPc"
End Function
0
Using Access 2003 and I have the following command: DoCmd.OutputTo acOutputReport, RptName, "SnapshotFormat(*.snp)", strPathandFileName

The report it changes to a snapshot format uses arguments to format the report, is it possible to pass arguments with this type of command?
0
win10, access 2010,vba,runtime. This has been working fine, but now I'm getting an undefined variable error on a set statement:
"Set myOutlook = CreateObject("Outlook.Application")"
myOutlook is the undefined variable error. this has been working fine for months.
other supporting info:
there are 2 identical instances of this in the app, the other works fine
no missing references
0
I have written some MS VBA code to scan a document on my local scanner and save the resulting scan in a particular folder with a particular file name. It arrives there as a BMP file, which is unacceptably large in file size (typically 25 MB). I have a CutePDF and PDF Creator and MS Print To PDF as PDF printers to choose from, if needed, but I cannot figure out how to print the BMP file to them by using some effective VBA code.

So, using MS Access VBA, how do I select the BMP file, convert it to PDF and save it in the same folder with the same name (except with a PDF extension, of course)? When I do this manually I get a PDF of around 500 kb, which is much more acceptable in size.

The end result I need is for the user to place a document, like a tax file number application, on the scanner, click a button on the database and have the system scan the doc, file it in the employee's folder on the server with a name indicating that it is his or her tax file number application and then automatically convert the resulting BMP to PDF and finally to delete the BMP file to save space on the server.

I can do all of this except the actual conversion of the BMP to PDF. Can any one help me here? My environment is Win-10, Access 2016. Thanks
0
In my query I have this:

query
Which results in this:

2017-10-20_15-41-16.bmp
Obviously not correct. How to fix?
0
Hello,

I am trying to figure out a way to come up with a helper column. I need to show all dates that are 4 weeks old and 13 weeks old based on today's date now((). also, please note that Sunday is the beginning of the week and Saturday is the end of the week

your help is greatly appreciated ..
0
I'm using the code below to try and delete all Excel workbook connections, however I get the following error:
Run-time error '424' Object required on line  Do While ActiveWorkbook.Connections.Count > 0
What additional code is needed to execute? Thanks


 Set objApp = CreateObject("Excel.Application")
         objApp.Visible = True
         Set wb = objApp.Workbooks.Open("C:\Users\vincent.Shis\Documents\ALL_Reports.XLSX", True, False)
     
 
   Do While ActiveWorkbook.Connections.Count > 0
      ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
   Loop


    wb.SaveAs "C:\Users\vincent.shields\Documents\ALL_Reports_Test.XLSX"
    wb.Close
    Set objApp = Nothing

    wb.Close
    Set objApp = Nothing

   objApp.Quit
     Set objApp = Nothing
0
An expert helped me out yesterday with this question:

"How autonumber field in subform section of a form"

But I just ran into a problem.  If the user entered 6 digits in the first field and then for example 9 in the 2nd field, I get an overflow error when I click [Proceed].  If the user enters anything less than 6 digits in the first field it worked fine.

Here is my current code: (Note I commented one line out because I was getting 1 more record than requested.

Private Sub cmdProceed_Click()

    Dim intLoop As Integer
    Dim db As dao.Database
    Dim rs As dao.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblLoadsDetail")

'    For intLoop = Nz(Me.txtStartingN, 1) To Nz(Me.txtStartingN, 1) + Nz(Me.txtRecordCount, 0)
    For intLoop = Nz(Me.txtStartingN, 1) To Nz(Me.txtStartingN, 1) + Nz(Me.txtRecordCount - 1, 0)
        rs.AddNew
        rs!LoadRecordID = Forms!frmLoadsHeader.txtLoadRecordID
        rs!LoadID = intLoop
        rs.Update
    Next

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    DoCmd.Close , ""
    
    Forms!frmLoadsHeader.Refresh
    Forms!frmLoadsHeader.cmdFocus.SetFocus
    Forms!frmLoadsHeader.cmdAutoNumberDetailLoadIDRecords.Visible = False

End Sub

Open in new window

0
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

I have a table that contains a field "Risk" The values in this are either "LOW", "MEDIUM" or "HIGH".
I need to report on this but have HIGH at the top of the report, then MEDIUM etc.

I think I need to add a field in my query that can assign a numeric value dependent on the contents of the Risk field.

Thanks in advance for any help
0
Sorry to bother you again

I want to prevent captured data from be truncation in Ms Access due to  text control width. For example :

(1) When I enter data in a narrow width text control , once it reaches the end of control it should stop accepting further entry. Example if the text control Employee Name  require  ( John Peter Andrew) to be entered, but due to its width lets say it can only accommodate (John Peter) the remaining part should be rejected due to width, that will be better , I have noticed some fan truncation on the reports . I want to prevent this once and for all

Regards

Chris
0
Hi Experts!

Is it possible to display the queries results on a form NOT a report, I needed this for the purpose of the bank reconciliation in my Ms Access application for example:
(1)      I want to see all the debits as a list in that form after filtering by date
(2)      I want to see all the credits as a list in that form after filtering by date
If this is possible then the rest I will handle it as long as the above can be seen on a form.


Regards

Chris
0
I need to get data from teachers about the courses they are giving on a particular day.

I currently have an access database where the data is input its a local database on a pc that is not on all the time.

I thought I might have a webpage that the teachers could input the data.

I have a synology nas ds411.

So maybe some guidance on the best direction for me to go to achieve this there seem to be many technolgies and I need something simple to implement.

I have sqlexpress on my local machine too but as I said thats not on all the time.

regards in advance
0
Hi Experts,

I have the following function
Function ViewPic()
'View Pic From DB
Dim OutFile As String
Dim strSql As String
If Len(Me.Signature & "") < 20 Then Exit Function
   
   Dim str As String
   str = "{" & """values""" & ":" & Me.Signature & "}"
  


        Dim Json As Dictionary
        Set Json = JsonConverter.ParseJson(str)
        Dim Values As Variant
     
        
        Dim Value As Dictionary
    
        Report_Report1.ScaleMode = 3
        
        For Each Value In Json("values")
         Report_Report1.Line (Value("lx"), Value("ly"))-(Value("mx"), Value("my")), vbRed
        
        Next Value

End Function

Open in new window

While in one database it runs file, when I copy that to another DB and run it (in report Detail format section event like in other DB), it gives me an error Object required in following line
        Set Json = JsonConverter.ParseJson(str)

Open in new window

1- I made sure it has all library references from the other DB
2- Also imported the general module JsonConverter that other DB had for this.
'Attribute VB_Name = "JsonConverter"
''
' VBA-JSON v2.2.3
' (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON
'
' JSON Converter for VBA
'
' Errors:
' 10001 - JSON parse error
'
' @class JsonConverter
' @author tim.hall.engr@gmail.com
' @license MIT (http://www.opensource.org/licenses/mit-license.php)
'' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
'
' Based originally on vba-json (with extensive

Open in new window

0
Using SQL Server 2014.

I need to parse off the last two segments of a variable length string from a column in a customers database.  In Access, I would simply use either Instrrev() or the Split() functions to perform this, but would like to do it in a SQL query.

Is there an equivalent to the Access instrrev() function in TSQL, which allows you to search for the character postion from right to left in a string as in:

?instrRev("ABC DEF GHI", " ") => 8

without having to work from left to right?

How about an equivalent to the SPLIT() function which parses a string into an array based on the delimiter provided:
Arr() = SPLIT("ABC DEF GHI", " ")
yields
Arr(0) = "ABC"
Arr(1) = "DEF"
Arr(2) = "GHI"

Thanks,
Dale
0
Hi, all. I have the following select statement behind an access query. I need to remove the portion after "WHERE (((Survey_Data.Inv_Date)" and use a statement to pull yesterday's date instead of using the beginning and ending dates user enters on the Reporting_Menu form.

Full statement:

SELECT Survey_Data.Store, StoreAsFourDigits.StoreCode, Survey_Data.Amount, Mid([filename],17,1) AS InvCo, Survey_Data.[Line#], StoreAsFourDigits.[Dstr Mgr], StoreAsFourDigits.[Rgn Nm]
FROM Survey_Data LEFT JOIN StoreAsFourDigits ON Survey_Data.Store = StoreAsFourDigits.StoreCode
WHERE (((Survey_Data.Inv_Date) Between [Forms]![Reporting_Menu]![BegDate] And [Forms]![Reporting_Menu]![EndDate]))
GROUP BY Survey_Data.Store, StoreAsFourDigits.StoreCode, Survey_Data.Amount, Mid([filename],17,1), Survey_Data.[Line#], StoreAsFourDigits.[Dstr Mgr], StoreAsFourDigits.[Rgn Nm]
HAVING (((Survey_Data.Amount) Is Not Null) AND ((Survey_Data.[Line#])="100" Or (Survey_Data.[Line#])="201"


I tried WHERE (((Survey_Data.Inv_Date) = (Date() -1) and get "Syntax error (mission operator) in query expression and it is highlighting GROUP. I also tried Date() - 1 with no additional parenthesis. Same error.

Am I just way off?
0
Please see this SQL.  Right now it is summing the values by each account number for ALL records.  But somehow I need to present a form to the user where they select a year and then have the crosstab query display the totals for each month for just that year.

TRANSFORM Sum(tblDataDetail.[NET_AMOUNT]) AS SumOfNET_AMOUNT
SELECT tblDataDetail.[ACCOUNT_NUMBER], Sum(tblDataDetail.[NET_AMOUNT]) AS [Total Of NET_AMOUNT]
FROM tblDataDetail
GROUP BY tblDataDetail.[ACCOUNT_NUMBER]
PIVOT Format([ACCOUNT_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

How would I change the SQL?
0
I receive survey data from multiple third party vendors.  The data comes to me in a crosstab table format in excel.  I need a way to make the table into regular format, so I can define relationships to other tables.   I need a vba procedure/function that can change the format automatically.

Example format - Question are S1, S2, S3, S4A
RespondentID      S1      S2      S3       S4A      S4B
1      1      1      2      1      
2      1      6      1      2      1
3      2      3      4      2      1
4      2      6      2      2      3
5      1      4      5      2      2

The format I needed
QuestionID      ResponseID      Value
S1                               1                 1
S2                               1                 1
S3                               1                 2
S4A                               1                 4A

I will include the access database I am working with.  The table I am trying to transform is called - M_RespondentAnswers. This needs to relate to other tables in relationship window. I trying to create something like a star schema in access, with survey being the fact table and the other tables like a dimensions for reporting purposes.
MIKE_SURVEYS_2017.accdb
individual_columnlisting.xlsx
0
NFR key for Veeam Agent for Linux
LVL 1
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

With much help from EE I have a parameter driven working mail merge procedure in my Access 2013 application

Depending on the parameters passed it will create and store a word doc and/or a PDF for each record in the input file.

As it is now, the word documents are created and appear briefly on the screen before the next doc is created.

I would like to add an option that to print each document that is created.  The option would be passed to the routine as a boolean parameter.  However, I'm  don't know where or how to modify the mail merge code to print each created document.

Can this be done?

Here is the current routine:
Public Sub CreateMailMerge_wPDF(passedDocumentType As Long, _
                                passedMergeDocNameAndLocation As String, _
                                passedDatabaseOrTextInput As String, _
                                passedInputTableOrQueryOrTEXT As String, _
                                passedOutputWordDocLocation As String, _
                                passedOutputPDFLocation As String, _
                                passedGenerateWordDoc As Boolean, _
                                passedGeneratePDF As Boolean, _
                                passedWriteToLog As Boolean)

Dim wkDocType As String
'
'
Dim NumPages As Long
Dim wkCreatedName As String
'Dim wkSpecificReportID As String
Dim wkWordDocumentName As String
Dim wkOutputWordNameandLocation As String
Dim wkPDFName As String
Dim 

Open in new window

0
access 2010
form
subform

I have a form called "main"
subform called "dbo_t_redbook_pricing_escalation_detail_subform"

I need to loop through 2 columns
on the subform to see if my conditional formatting has changed the fore color/background color to red ?

background/fore color
if it does i need a msgbox or boolean alert.
I will  be calling the alert from a Command button on my main form.



Thanks
fordraiders
0
I know this is probably a simple question but I am going to ask it anyway.

I want to create usernames to be first initial of first name and the full last name.  How do I do this.

First name is stored in the field First_Name and Last name is stored in Last_Name.

Thanks
John
0
How do I add Dlookups to this report.  I tried putting them in the Detail section but that put them after every count.

report.png
0
For control purpose I was thinking that, if  a sales tax invoice is approved in the application then it should never be reprinted again the same way we do not allow the approved sales invoice not to be deleted. I know we can still delete unapproved invoice but once approved it is locked , now I want to do the same with reprinting:

(1) I'm thinking of designing a VBA code like below:

Dim Cancel as Interger
IF " approved" = Dlookup(" Status", " tblCustomerInvoices"," [InvoiceID] = " &Me.CboInvoiceID) Then
MsgBox " Please note that approved invoices cannot be reprinted"
Cancel = True
Exit Sub
End If

Can the above code stop the printing  or are there better ways of doing it , any suggestion is very welcome

I have not yet tried the code, I can clearly state here that the code is my own thinking in term of financial internal controls

By the way assuming it is okay which event should I place it on a form used to print the invoices

Regards

Chris
0
What is the Access function that provides the same results as MS Excel proper(A,1)
0

Microsoft Access

217K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.