Microsoft Access

221K

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

Is it possible to generate chart (as shown in the attached image) along with various information on the left side of chart and below the chart using MS Access?



The information for the plots on the chart as well as different labels is coming from different tables.

How to accomplish this?
So far I have written few queries to return data for multiple series.
Also I have created few labels on report and linked them to table fields.
I am not getting how to plot multi series line chart and also the data below chart.
0


is it possible to generate a chart in access report (as shown in the picture) in addition to the different information shown on left side of the chart and below the chart.
The data that is plotted on chart and also all the information labels are coming from different tables.
Please let me know how to accomplish this.

Thank you
0
I would like the form data to be aligned horizontally instead of vertically. Here is the snapshot of form that is currently displayed.

Current form alignmentI would like to change this so that it looks something like this:

Subject      56-020      56-020      56-020      56-020      56-020

TargetDays      .      0      1      2      6

MAP      80      67      84      73      85


Sorry, I was unable to format this as a table. But I hope what I am trying to ask is clear. I just want to change the alignment so that column headers are showed as row headers and their corresponding values are display horizontally instead of vertically. Please kindly let me know how to accomplish this in MS Access form. Thank you.
Form_Snapshot.PNG
0
I have a vba code I've modified to import data from excel but some of the data does not meet the field validations. I need to have that row with errors to be created in a new table. This is my code for importing:

Public Function GetMyFile() As Boolean
On Error Resume Next
Dim fdFilePick As FileDialog
Dim varSelectedFile As Variant
Dim strInFile As String
Dim strImport70 As String
Set fdFilePick = Application.FileDialog(msoFileDialogFilePicker)
DoCmd.Hourglass True
    With fdFilePick
        .AllowMultiSelect = False
        .ButtonName = "&Import"
        .InitialFileName = Application.CurrentProject.Path
        .Filters.Add "Excel", "*.xlsx"
            If .Show Then
                For Each varSelectedFile In .SelectedItems
                    strInFile = CStr(varSelectedFile)
                    DoCmd.DeleteObject acTable, strImport70
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Import70", strInFile, True
                Next
            Else
                MsgBox "Import Process Canceled", vbCritical, "Import Canceled"
                GetMyFile = False
                Exit Function
            End If
    End With
End Function

Open in new window

0
Hello.  I have been presented with a requirement that has the attachment being added to the database but not by using the Attachment datatype or the Attachment control.  This makes one attachment per line rather than the attachments all in one field.  Some examples I was shown has anywhere from 1 to 61 attachments in the one attachment field.  In the past I have added the path of the attachment using the code below.  Now, the attachment itself is to be added, not the pathway.  In the past I have provided the plus and minus for this and even did the pathway only at one time.  Needless to say, i lost weight after that from being chewed on by others.  So, I do what they want.  So please spare me the lecture.  The attachment can be of any type of document type (pdf, word, excel, powerpoint, picture, etc).  The user will need to be able to add, open, delete the attachment.  The delete part is questionable for now since they are still hashing that one over.  To verify, would the datatype for the field be 'OLE Object'?  The code below should still be viable to use since it opens the dialog to select the file.  I am considering a continuous form with the buttons at the end of each row.  Would this be the way to go?  There needs to be a description field added since some attachment file names are numbers.  In the past when I did the pathway only, I used a regular unbound form with listbox and unbound fields with an insert statement behind button.

So, I need some guidance on this …
0
I have a report that contains labels, chart. I also have to include a table like controls one with vertical alignment of values and another with horizontal alignment of values. All the controls in report are information of  a patient. So a PatientID should be given as input to the chart. From here all the controls are populated by joining different tables on PatientID. Some values are directly from the table field, others are from Queries. Please let me know how can I accomplish this.
So far I am able to populate controls that are directly from a table field. But this is generating multiple reports with all patients. I am unable to give input to the report so that only one patient's report is generated. Also I am yet to accomplish incorporating Chart, table and controls that are bound to queries.
Please kindly help me in this regard. Thank you.
0
OK, so I have the following code (Code No. 1) that writes data from an Excel file into a PDF Form (I use Adobe Acrobat).
In addition, I have anothe rcode (Code No. 2), that pushes the data from the PDF Forms, back into the excel file.

Instead of doing these 2 things from/to Excel, I want to do it from my MS access database (let's say a table called "Data", with the same fields as the PDF Form fields), but I don't know how/where to change the code.

Any help would be greatly appreciated!


Code No. 1:

Option Explicit

Sub WritePDFForms()
     
    'Declaring the necessary variables.
    Dim strPDFPath              As String
    Dim strFieldNames(1 To 11)  As String
    Dim i                       As Long
    Dim j                       As Integer
    Dim LastRow                 As Long
    Dim objAcroApp              As Object
    Dim objAcroAVDoc            As Object
    Dim objAcroPDDoc            As Object
    Dim objJSO                  As Object
    Dim strPDFOutPath           As String
           
    'Disable screen flickering.
    Application.ScreenUpdating = False
   
    'Specify the path of the sample PDF form.
    'Full path example:
    'strPDFPath = "C:\Users\Christos\Desktop\Test Form.pdf"
    'Using workbook path:
    strPDFPath = ThisWorkbook.Path & "\" & "Test Form.pdf"
   
    'Set the required field names in the PDF form.
    strFieldNames(1) = "First Name"
    …
0
Hi,

I am looking for vba which needs to run from MS Access2013. This script should delete a particular sheet and replace that with a new set of data with same sheet name, keeping other sheets as they are because those sheets have formulas/functions looking into the deleted sheet.

Thanks in advance.

Thank you
Kevin
0
Hi,
I created a form with three custom filters using a list box in access 2016.  The list box contains 6 fields (Recreation ID, Recreation Vendor, Recreation Sport, Recreation Price).  I wanted to add a description column but the text is too big.  I looked into displaying the description for each Recreation Sport Program each time a Recreation Sport is selected from the list box (like a product in the example below) via 'On Click' or 'On Dbl Click' for each record selected but could not find an example and it seems like the list box does not support that capability.  I have checked the internet but coming up empty identifying examples or solutions that can help.

The form I created is based on the FormFilters.mdb linked to article: https://www.experts-exchange.com/articles/6070/Building-a-Custom-Filter.html: Building a Custom Filter - Building a filter (for the Products Table).

Thanks and any help will appreciated.
0
Hi,
How can i include a date variable in a select query in docmd and create a query out of that

Thanks
K
0
Hi,

Public Sub Export()
FilDatCretd = FileDateTime("Z:\Reports\Repository\ProReports\txtProData.txt")
   
'Format(FilDatCretd, "ddmmmyy hhmm") I need to add this part into a new sheet called XYZ on the same workbook<<<<<<<<<------------
DoCmd.TransferSpreadsheet acExport, 10, "QryRprtH", "C:\NatRep\Reports\Report.xlsx", True, "RprtHi"
DoCmd.TransferSpreadsheet acExport, 10, "QryRprtDt", "C:\NatRep\Reports\Report.xlsx", True, "RprtDt"
End Sub

Any help greatly appreciated.

Thanks
K
0
I'm using MS Access on the front end for a point-of-sale and Azure SQL Server tables on the back end.  Yesterday South Central US Azure went down which locked up (disabled) my front end.  

Does anybody have VBA code that would check if Azure is responding?  Perhaps I could run a simple query to Azure (almost like a ping) and if there is no response then I could bypass the rest of my queries.  I'm not a programmer so please provide the explicit code.

Thanks!
0
Need to Calculate Quantity on Hand,

I need to write some VBA Code and SQL Statement in a function that will Look at 2 different tables in an Access 2010 Database  and pull the Part # info from the “tblPartNum” and the following from the table “Inventory Transactions”
UnitsReceived
UnitsSold
UnitsShrinkage
UnitsUsed
UnitsCycleCountCorrection

Then I need to put this together in a formula to come up with the total quantity on hand

QtyOnHand = UnitsReceived - UnitsSold - UnitsShrinkage - UnitsUsed + UnitsCycleCountCorrection

This is for a database I am working on to track inventory for a manufacturing database (like a mini MRP). The “Inventory Transactions” table contains all records of transactions of Parts ordered, received, sold, shrinkage, used, and cycle count corrections

I am not that fluent in VBA or SQL statements and I can’t seem to get anything I attempt to work properly. I believe this needs to be in a function so I can reuse it. I also believe the best way is to do a Do While loop and keep stepping thru the “Inventory Transactions” table for each “Part Number” in the report but I am not sure.

Can someone please assist with the code.
0
Ok, I have an add-on question for one of my previous questions...

I previously needed to merge two tables from two separate databases with UNION ALL and I was able to accomplish that task. Now I need to combine two separate databases that had look-ups. I have found out that I can't use look-ups in my tables like I have been doing because it will bring in the ID#'s instead of the text values.

I have deleted my look-ups in the table and re-imported my field data. On my Form I am using cascading combo boxes (System, Component, Problem, Repair). When I selected the items in my combo boxes to test, it began displaying the ID#'s in my table instead of the text value. I discovered that I needed to change the Bound Column From 1 to 2 to get the System field to display the Text Value in my table. But now the Cascading Combo Boxes are not working. Component, Problem and Repair now only display blanks instead of the dependent list.

Form.JPGTable.JPGNOTE: the entries that do have text instead of ID# were entered with Bounding Column for that specific field set to 2 instead of 1.
Here are the properties for the Component Cascading Combo Box:
Properties.JPGProperties2.JPG
Thank you, any assistance is greatly appreciated!
0
Hi

My access database does rename after compact and repair. I have done tick on the options to compact when database quits.

Please have a look

Thx
0
I am calling an external application from MS Access in Vb6 and trying to change a combo box selection, but all the sendmessage constants I have tried do not work. Code I have so far successfully opens the App, navigates to the correct window, expands the combobox and thats as far as i get. I have also attached a link to the Spy ++ tree
Sub RunSailwaveUpdates()
Dim hwnd As Integer
Dim SortSelect As String
SortSelect = "Fleet"
hwnd = FindWindow(vbNullString, "Sailwave - 
C:\Users\Public\Documents\Sailwave\Results\Summer Points.blw")
start_doc = ShellExecute(0&, "open", " 
C:\Users\Public\Documents\Sailwave\Results\Summer Points.blw", 0, 0, 
SW_NORMAL)
If start_doc = 2 Then Exit Sub
If start_doc = 3 Then Exit Sub
Do
DoEvents
hwindow2 = FindWindow(vbNullString, "Sailwave - 
C:\Users\Public\Documents\Sailwave\Results\Summer Points.blw")
hwindow3 = FindWindowEx(hwindow2, ByVal 0&, "ClaChildClient", vbNullString)
scoreseriesbutton = FindWindowEx(hwindow3, 0&, "ClaButton_0400000H", "Score 
Series")
Loop Until hwindow2 > 0 And hwindow3 > 0 And scoreseriesbutton > 0
WaitSeconds (0.5)
Call SendMessage(scoreseriesbutton, BM_CLICK, 0, ByVal 0&)
Do
DoEvents
scoreseries = FindWindow(vbNullString, "Score Series")
scoreseries2 = FindWindowEx(scoreseries, 0&, "ClaChildClient", vbNullString)
groupoption = FindWindowEx(scoreseries2, 0&, "ClaRadio_0400000H", "Score 
groups of competitors separately  -  scoring system is applied to each 
group")
groupfield = FindWindowEx(scoreseries2, 0&, 

Open in new window

0
I have a Microsoft Access application that writes to the Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings using the SaveSetting function to provide a file name for Win2PDF. I have established that on one client computer the SaveSetting function does not appear to work.

I have cut it down to the following code for testing:

MsgBox "Before writing to Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\"
SaveSetting "ClientName", "ClientSection", "ClientKey", "ClientData"
MsgBox "After writing to Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\"

On all the computers here in the office this creates the registry entries. On one client computer both message boxes are displayed, there is no error caused by the SaveSetting line but no registry entries are created. Client computer details are:
Windows 10 Home, version 1803 (OS Build 17134.228) , 64 Bit
Access (Office 2016 32 Bit), Version 1807 (Build 10325.20118)
0
I had this question after viewing Interface between RingCentral and Access/VBA.

Hi,

I am just looking to follow up with this as I am trying to build a similar function linking our Access Database to Ring Central.  Ideally having a button next to a customers telephone number which when clicked links into Ring Central and makes the call.

Any help on this would be greatly appreciated.

Thanks
Dominic
0
I have an asp page which has several fields and I got stuck at drop down button which has to populate data from a query.
But whenever I wrote some code taken from different examples from online I will get http404 error.

How to overcome it.
 my sql query is select cur_date_str,cur_effective_date from tblmovedate; where I need drop down to be filled with cur_effective_date data.

Please help me
0
Can't clear, or prevent, Microsoft Access lock file.
0
it is possible to programmatically pass a parameter to a group of queries that require the same parameter?

The parameter i need to pass to all the queries is ProjectName.

This is my code for the procedure that groups all the queries together and exports to an Excel template.

Option Compare Database
Option Explicit

Public Sub ExporttoExcelCS()

    Dim XL As Excel.Application
    Dim wbTarget As Workbook
    
    Dim qdfCCTV As QueryDef
    Dim qdfFire As QueryDef
    Dim qdfHHLA As QueryDef
    Dim qdfPC As QueryDef
    'Dim qdfSec As QueryDef
    Dim qdfESD As QueryDef
    Dim qdfInst As QueryDef
    Dim qdfGantry As QueryDef
    Dim qdfLight As QueryDef
    Dim qdfPM As QueryDef
    Dim qdfComms As QueryDef
    Dim qdfCSTest As QueryDef
    Dim rsCCTV As DAO.Recordset
    Dim rsFire As DAO.Recordset
    Dim rsHHLA As DAO.Recordset
    Dim rsPC As DAO.Recordset
    'Dim rsSec As DAO.Recordset
    Dim rsESD As DAO.Recordset
    Dim rsInst As DAO.Recordset
    Dim rsGantry As DAO.Recordset
    Dim rsLight As DAO.Recordset
    Dim rsPM As DAO.Recordset
    Dim rsComms As DAO.Recordset
    'Dim rsCSTest As DAO.Recordset
    
    'Setup the reference to the query to export
    Set qdfCCTV = CurrentDb.QueryDefs("qry_CCTV")
    Set qdfFire = CurrentDb.QueryDefs("qry_Fire")
    Set qdfHHLA = CurrentDb.QueryDefs("qry_HHLA")
    Set qdfPC = CurrentDb.QueryDefs("qry_PC")
    'Set qdfSec = CurrentDb.QueryDefs("qry_Sec")
    Set qdfESD = CurrentDb.QueryDefs("qry_ESD")
  

Open in new window

0
Hi guys

I have been trying for weeks to get an Update and Insert Function to work.

The scenario is Import from excel spreadsheet to a temp table then from that temp table i want to be able to match and update or add records in the main tables. So diagrammatically it goes like this:

excel spreadsheet - import to temp table. (No ID Fields)(I have this side working really well)
Temp table - Update Main Table if record exists if not add record. (Once this is complete Delete temp table data)

Is this possible? If so how do i do it. I would prefer using VBA behind a command button.

I have tried different coding structures but i can not make it work.

Public Sub UpdateCS()
Dim SQLMove As String
    Dim SQLReplace As String

    SQLMove = "INSERT INTO tbl_CS SELECT * FROM tbl_TempImportCS"
    SQLReplace = "UPDATE tbl_CS " & _
                 "INNER JOIN tbl_TempImportCS ON tbl_CS.Classification = tbl_TempImportCS.Classification " & _
                         "SET tbl_CS.DrawingReference = tbl_TempImportCS.DrawingReference, " & _
                         "    tbl_CS.DrawingReference2 = tbl_TempImportCS.DrawingReference2, " & _
                         "    tbl_CS.CableType = tbl_TempImportCS.CableType, " & _
                         "    tbl_CS.Prefix = tbl_TempImportCS.Prefix, " & _
                         "    tbl_CS.Type = tbl_TempImportCS.Type, " & _
                         "    tbl_CS.Cores = tbl_TempImportCS.Cores, " & _
                         "    

Open in new window

0
I have a single table of courses a student took and the grade. I want to do a count of how many times a student failed courses and include zero counts.

Edit:
I thought of something different. So now I have 2 tables, a student data table and a student courses tables, joined by student ID. Is there a way to create a query and do an outer join to bring the student fail count in for the students that have a count, so for students where a fail count does not exist, the field is blank?


Thank you for any assistance.
0
Hi Experts!

Please help me create a button Click() sub that exports info on an Access 2016 form including subform to an Excel spread sheet. No script I can find seem to work. I'm new to VBA and Access so my knowledge only extends to adding an [Event Procedure] to a button.

The goal is to append to an Excel spreadsheet the data from an Access form (including subform) each time a new record is added. The spreadsheet would be used as a backup document.

Thanks!
0
I am using Access 2016 MDB file with VBA and Links to Share Point Foundation Lists.
When I REFRESH the SPF list via selecting the list and right mouse more options "Refresh List" in access it updates fine.
In VBA the only option I can find is to RefreshLink, this disassociates the link from the custom group in the Navigation pane and creates a new ID.
Currently I have code that reads and writes the MSys tables to put back the Linked table in the appropriate  Hidden Custom group.
My preference is not to have to read and write the MSys tables.
0

Microsoft Access

221K

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.