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

I am using an Access 2016 split database with the backend table on a network folder. The front ends are on each computer. That said, when I try to open the tables in my front end, I am getting an error saying "recordsets are not updateable". Further, if I try to open the tables with another user using the tables from another computer, I get locked out.
1
Im trying to create a nested IFF function to turn this excel function in access. The basic excel formula is =IFERROR(SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!F:F,"*COMP*",'Import Report'!O:O,">="&(A2-1/24),'Import Report'!O:O,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE"))/SUM(COUNTIFS('Import Report'!E:E,{"CM","MMNRO","MMROI","PMCM","PMINS","PMOR","PMPDM","PMREG","PMRT"},'Import Report'!K:K,">="&(A2-1/24),'Import Report'!K:K,"<"&(C2+23/24),'Import Report'!X:X,"LEWMXTCHE")),0)
0
For a stored query (name is "CREAT_Plot") like the following in Access, what would be the vb.net or C# code to call this query and also pass a parameter in place of "[Reports]![TestReport]![Subject]"

SELECT CREAT.Subject, CREAT.CREAT, (IIf([CREAT.TargetDays]=".",".",Val([CREAT.TargetDays])*24)) AS TargetDays
FROM CREAT
WHERE (((CREAT.Subject)=([Reports]![TestReport]![Subject])))
ORDER BY CREAT.TargetDays;

I have googled about this but could not find an example to call stored query that prompts for input parameters.
Please kindly give me small code snippet to achieve this one.

Thank you
0
I have a form “frmObrasAlbum” where I have one combo, one textbox and a subform “frmInterpretesSub”.

I need to trigger a unique event in the subform when I leave the parent form (and entre in the child form), so the child form is automatically filled by a vba procedure (then I can edit the default values or accept those generated by the code).

My problem is, I cannot use an event of one of the controls of the child form because when I edit the default value of that control the vba code is triggered again (because it is linked to that control).

I tried to use “form” events, like on enter/set focus/got focus, but I does not fire the vba procedure.

Can someone give me an advice of which way I should go?
0
I am trying to generate a chart on which I have to plot multiple line series. (I have no clue how to send data for multiple line series.)
As a first step, I tried creating a chart and plotting one line series on it.

Here is how the data is

Subject      CREAT      TargetDays
22-001      .      0
22-001      1.4      24
22-001      1.1      48
22-001      0.8      480
22-001      0.8      672
22-001      0.8      216

While creating a chart on report, I followed the following steps:
1. I have selected the query that is generating the above data
2. I have included all fields that will be used for this chart.
3. I have selected the chart type as 'Line Chart'.
4. I have dragged and dropped the fields that are to be shown on x-axis and y-aixs. In my case, x-axis values are 'TargetDays' and y-axis values are CREAT
5. Then I linked Subject of this chart to the Subject of the report on which this chart is placed.

I saved the report and tried to run it. But interestingly and confusingly this is what is generated (as shown in the attached image)



I have no clue why would it generated a chart like that which is completely out of data that I am passing into it. Any help in this regard is highly appreciated.
0
Hi,

I'd like to create charts based on selected listbox items & entered dates from a form.

The first graph works fine, but the second one isn't loading the correct data.

I think the problem is that the second table uses "ID" instead of "WorkerID" and the main form's record source

is based on a specific table.

I've tried to create relationships between tables  tWorker,tReg and tAbsence, but it did not recognize WorkerID in tReg.

Is there a solution to this?

Thank you.
Charts_14-09-2018.accdb
0
I am doing an internship at an insurance Company and I have been tasked to create an employee/facility management database with Microsoft Access 2016.

Although I am still in an early phase of development (eliciting information/requirements), I have been asked whether it is possible to display a different ribbon based on the login provided.

I am using RibbonCreator and I understand that it is possible to have several ribbons in an Office document.
Is there a way with VBA to change the ribbon based on the login data provided (there are only four logins)?

It looks like that the data is stored in a table called USysRibbons with the following structure:
lngID 
RibbonName
Ribbon XML

Open in new window


I understand that it is possible to change the Ribbon inside Access, but it requires a restart of the application.
Would the loading of a different Ribbon with VBA require the same procedure?

Another idea which I have in my mind is that only one customised ribbon with several tabs is shown.
I would then hide or show these tabs based on the login provided.
Is this an easier approach?

Thanks in advance for your feedback which I can put inside the functional requirements.

Massimo
0
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

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.