Microsoft Access

220K

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 have two main tables where an employee (Employees) saves the date and hours spent on a task in a table called TimeSheetData.
The query I want is basically give me the last date each employee has saved time which would pull from the fieldname:  DateEntered.

It would look like:

Employee               DateTimeWasSubmitted

EmployeeA              3/21/2019
EmployeeB              2/1/2019
EmployeeC      
EmployeeD              1/14/2019

I provided a Microsoft Access sample - note how some employees maybe blank cause the DateEntered did not get out.  
How would I get this accomplished?  I want it to list all employees whether time was entered or not.
ee-question-last-date-emp-entered-.accdb
0
Active Protection takes the fight to cryptojacking
LVL 2
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

I'm trying to open a specific form's record via a double-click event on another form.  Here are the things I've tried but no matter what code I use, the form opens to the first record.

DoCmd.OpenForm "frmClients", , , "[ClientID]=" & Me.txtClientID
and
DoCmd.OpenForm "frmClients", , , "ClientID = " & Me.txtClientID
and
DoCmd.OpenForm "frmClients", WhereCondition:="ClientID=" & Me.txtClientID

What is wrong with this?
0
Hi Experts there!
I’m thinking of dropping all bound subforms and replacing them with unbound forms to try and avoid the following:
(1)      Avoiding locking system and significant gain on performance, this will have done by using append queries with the help of VBA, for sure I have to match the data reference to the primary table.
What is your advice on this one? Any problem you anticipate in future?

Regards

Chris
0
Access:  Looking for help in setting up a db.
How would I set up a table to issue tasks to be done?  I have a table with the tasks which are associated with a piece of equipment. Each task has an interval (time based in hours) that the task needs to be done. I would want to set a start date and then once I signal that it has been completed I would need for a next due date to be calculated based on the previous due date and interval. I would also need a field for documenting any issues so they could be addressed.
0
Dim rstTBDSectionWithDashCount As Recordset
Set db = CurrentDb

strImportTableMR = "5-1"
rstTBDSectionWithDashCount.Open "Select * From tbl_First_Occurrences_Report where tbl_First_Occurrences_Report.txt_Section_With_Dash = '" & strImportTableMR & "'"
rstTBDSectionWithDashCount.MoveLast
intrstTBDSectionWithDashCount = rstTBDSectionWithDashCount.RecordCount

Hello All.
I am getting an error message because "OPEN" is not in the method list.
What I am trying to do is.

Look in Table:
tbl_First_Occurrences_Report

In Field:
txt_Section_With_Dash

With the value of"
5-1

For this particular case my final goal is to get the Number of Records for the variable.

Thanks
Nate
0
Hi All,

I'm having an issue with a message box displaying properly on *one* newer computer, access 2003.

Design is:

Base form, maximized.
Base form populates a modal form (modal = yes, popup = yes).
The user clicks a button, which runs the following code:

This runs the following
    VerifyAddress 'this calls an API which checks against the address, this is working properly.

    Dim Response As VbMsgBoxResult

    Me.Dirty = False  'ensures the info from verifyAddress is saved.

    If Not IsNull(DLookup("[CustomerId]", "[dbo_tblOpenOrders]", "[CustomerId] = " & Me.CustomerId)) Then
        Response = MsgBox("This customer already has an open order!" & vbCrLf & _
                        "If you would like to add an additional order, " & vbCrLf & _
                        "click 'Yes', otherwise, click 'No'.", vbCritical + vbYesNo, "Open Return Msgbox")
        If Response = vbNo Then Exit Sub
    End If

Open in new window


The message box appears to be popping *UNDER* the modal form.  If I press the alt key the message box will show up.  Also if I hit enter it looks like it's selecting yes.

We have other newer computers that work fine, it's just this one that is causing an issue.

Scratching my head and looking for ideas.  

We've compacted and repair.
Project compiles fine (on a good machine as well as the bad machine).
No missing references.

Thoughts?
0
Access update query

I need to pull the first word from TaskDescrip  and update Activity field with that word

SELECT tbl_Tasking.Activity, tbl_Tasking.TaskDescrip
FROM tbl_Tasking;
0
I am upgrading a client's legacy Access 2003 application.  We are using the 2016 version of MS Access.

I recommended the IT people install the 32 bit version of Access 2016 instead of the 64 bit version.  Based on my experience and other developers experience I have always specified the 32 bit version.  I've even read that Microsoft also recommended using 32 bit.

IT responded that within the last couple months Microsoft has changed their recomendation to 64 bit.

Have any of you heard that?

Even if it's true does it only apply to the 2019 version of Office?  In this case we are using Access 2016.  I'm not sure if the recommendation would extend backward.
0
I made a form to browse images held in an ImageList control (named Images).
The form also has 2 buttons to navigate forward (named Next) and backward (named Previous), as well as an Image control (named ImageDisplay) to display the current image.

So far, my attempts to display something failed.
My code is fairly simple:
Option Compare Database
Option Explicit

Private mIl As MSComctlLib.ImageList    '// reference to the ImageList control
Private mIndex As Long                  '// index of currently displayed image

Private Sub Form_Load()
    mIndex = 1
    Set mIl = Images.Object
    
    DisplayImage
End Sub

Private Sub Next_Click()
    If (mIndex < mIl.ListImages.Count) Then
        mIndex = mIndex + 1
    End If
    DisplayImage
End Sub

Private Sub Previous_Click()
    If (mIndex > 1) Then
        mIndex = mIndex - 1
    End If
    DisplayImage
End Sub

Private Sub DisplayImage()
    ImageDisplay.Picture = mIl.ListImages(mIndex).Picture    '// failure here, the Picture property return the image's handle
End Sub

Open in new window

So, how can I display images held in an ImageList control, in an Image control ?
0
Hi Experts,
I'm working on upgrade Access 2010 to Access 2016, I encounter a lot of problems with access 2010 32bit to access 2016 64 bit.  First was the modules that I had a ticket for that, after the experts helped, it seems no error message now. but I have couple problems again.  here are my questions:
1).  If I still use Access 2010 format not import my Database to Access 2016 format, it works well after I fixed the module, does any know if I keep the access 2010 format on access 2016, is there any problem in the future?  my current Access 2010 is using the Access 2007 format (but they are both 32 bit and now access 2016 is 64bit).

2). after I import my database from access 2010 format 32bit to access 2016 format 64bit, after I fixed the module, the other problem is there is "compile error: user-defined type not defined" on "dim Db as Database".  I check the References:
   a), checked Visual Basic for Application
   b). Microsoft Access 16.0 object library
  c). OLE automation
  d). Microsoft ActiveX Date Object 2.1 library
  e). Microsoft DAO 3.6 Object Library  however this works on Access 2010 format 32Bit, but when I check it on Access 2016 format 64bit, it gave me error message "Error in Loading DLL"

Can any one know how to fix or any suggestions?  

Thanks
0
Get a highly available system for cyber protection
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Access - Query is not returning blanks when I use   Not Like "Delete"

I should be getting lines with blanks and lines with other text in the field ACTIONTAKEN????????

SELECT tbl_MECHANICAL.TAG_NUMBER, Calendar.MDate, Calendar.MaintenanceID, tbl_MECHANICAL.New_Crafts, tbl_MECHANICAL.Unit, tbl_MECHANICAL.ASSETSDescription, tbl_MECHANICAL.Duration, tbl_MECHANICAL.TASKSDescriptionCorrection, tbl_MECHANICAL.New_Interval, tbl_MECHANICAL.Unit, tbl_MECHANICAL.System, tbl_MECHANICAL.Time, tbl_MECHANICAL.ActionTaken
FROM Calendar INNER JOIN tbl_MECHANICAL ON Calendar.MaintenanceID = tbl_MECHANICAL.ID;
0
Hi Experts;

I need a clear advice on the Ms Access tables storage size:
Our client with a supermarket has many products on the shelves, now our current Point of sales works as follows:
(1)      For every product line, five line entries are generated as follows (Revenue line, Vat Line, Cost of Sales Line, Stock line and the Cash/Receipt line)
(2)      All the five lines are stored in POS Table details
My question or worry is can this table manage to handle let say 850, 000 product sold per year which is equal (850000 X 5 lines = 4,250,000 lines) if an access table can handle that, then can we continue to use the same POS for the next 5 years? Will it not burst???????
The above line represent data that will be required for accounting purpose, example:
(1)      Revenue Account
(2)      Cost of sales Account
(3)      Vat output Account
(4)      Stock Account
(5)      Cash/Receipts Account
The performance as at now is very good no issues at all.
Regards

Chris
0
I recently upgrade a clients front end from an Access 2003 mdb running on a Win 7 / Access2007 machine.  The Win 7 / Access 2007 environment had been operating without issue for nine years.

The front end is now Access 2016 running on a Win 10 machine.  Most of the functionality is exactly the same.  One partricular unchanged piece of logic which ran perfectly for nine years in the old environment years cancels with a "not Enough Memory resources are available to complete this operation'' error on the Win 10/Access 2016 machine.  The physical ram on the new machine is double the ram on the Win 7 Access 2007 machine.

Does anyone have a reason this may be happening.  Is there something inherently different about memory management in the Win 10 ./ Access 2016 environment?
0
Recurring Access MDB data corruption

I am upgrading a Access 2003 application to Access 2016 for a client.  The application has about 8 - 12 users.

For the last nine years each user had their local copy of the FE mdb.  The data MDB resided on the server and all user were linked to it.  Nine years, no corruption issues.

During that time all users had Access 2007 and Windows 7 installed on their local machines.

The client wants to move to Win 10/ Access 2016.

I upgrade the FE application to an accdb.  Most functionality in the application stayed the same but some of the functionality, mostly the export of reports in preview mode had ot be revised since the old Access 2003 'Export' threw an error in Access 2013 and 2016.

I wanted to a controlled rollout to find any problems in the Accdb prior to rolling it out to all users.  The client purchased two WIn 10/Access 2016 machines.  I installed the upgraded application on those machines.  The backend DB is still and MDB.    All users (Win 7/Access 2007) and WIn 10 Access 2016) connect to the same backend MDB

The first couple of days went smoothly with about 7 users on the old Win 7/Access 2007 version and 2 user on the new Win10/2016 versionbut now their seems ot be a data corruption issue almost daily.  The users get '3343 Unrecognized database format'.

Given this scenario, does anyone see a reason this issue is occurring?

Several imes in the past I have had clients with a mix operating systems and Access…
0
I have a table called "Parameters" in Access 2010 that contains ranges of years by Category. I'm looking for the SQL that would DELETE all records from the "dataTable" where the range of years for each category does NOT fall within the range of years found in the "Parameters" table by category.

Below is a picture of the "Parameters" table and the "dataTable". The records highlighted in RED are the records that would be deleted since they are outside of the "years" range in the "Parameters" table.

Thanks in advance for your help!

table 1
table 2
0
The data is stored in this way:  Year, Quarter (1st, 2nd, 3rd, 4th), DatabaseName, tablespace_name, Size(MB)


I have an access database that allows the user to choose the database name (from a drop down),
then (based on the database chosen) choose the "from" date, then the "to" date.  This gives the growth of the data during the selected time period.
It works OK but I'd like to revamp the database.

I've created a table for each database to hold the data for each database. See samples below for databases "FINA" and "PAYR".
 

TABLE:  tbl-FINA_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST      FINA         FINA_DATA   29383
2017  2ND      FINA         FINA_DATA   33228
2018  1ST      FINA         FINA_DATA   38222


TABLE:   tbl-PAYR_Data

YEAR  QUARTER  APPLICATION  TABLESPACE  SIZE(KB)
2017  1ST     PAYR          PAYR_DATA   3892983
2017  2ND     PAYR          PAYR_DATA   4493903
2018  1ST     PAYR          PAYR_DATA   4899393
2018  4TH     PAYR          PAYR_DATA   5300203


I also have a table of the database names:

TABLE: tbl-DB_List

DBCODE  DBDESC
FINA    FINANCIAL
PAYR    PAYROLL

I made a form that populates a drop down box of the Databases from tbl-DB_LIST

When the user selects the Database name form the list, I want to do the following:

1. Obtain the DBCODE (this I can do)
2. Plug the DBCODE into a variable that is used to construct the name of the query.  This query selects distinct Year & Quarter …
0
I have a form bound to a table that has several fields on it.  One of them is a number field, standard, 0 decimals.  If the user enters a "2" in the field, and the user clicks a [Copy] command button, I want the current record to be copied 1 time.  In other words, end up with two records with all of the data copied exactly except for one field.  The field that can't be copied is a date field.  For this field the logic it to make the one copy but advance the date by one day in the copied record, Saturday's, Sunday's, and holidays included.

If the user enters a "3" in the number field, I want the current record to be copied 2 times.  In other words, end up with three records with all of the data copied exactly except for the date field.  For this date field the logic it to make the two copies but advance the date by one day for each copy, Saturday's, Sunday's, and holidays included.

I sure hope I've explained this well.
0
I have a table called "Parameters" in Access 2010 that contains a ranges of years by Category that I'd like to retrieve

Parameters table
I have a data table that I'd like to retrieve records from on "years" that fall within the minimum and maximum year range by category on the Parameters table. For example, based upon my parameters, the data highlighted in yellow below is the data that would be retrieved.

Data
I appreciate any recommendations as to what would be the most efficient way to execute this. Ideally, I'd prefer to do this through SQL, but if this can't be achieved in a single query, I'm open to running a VBA function or whatever you think is best.

Thanks in advance for any suggestions!
0
I have a code sub that I need help with. Both the data source and the form control name are both DepartmentID. Is there an easy way to identify the data name from the form control name?

Private Sub DepartmentID_DblClick(Cancel As Integer)
On Error GoTo Err_DepartmentID_DblClick
    Dim lngDepartmentID As Long

' I believe the first DepartmentID is the table field name and the second is the form control because of the .Text=""  
 If IsNull(Me![DepartmentID]) Then
        Me![DepartmentID].Text = ""
    Else
        lngDepartmentID = Me![DepartmentID]
        Me![DepartmentID] = Null
    End If
    DoCmd.OpenForm "Departments", , , , , acDialog, "GotoNew"
    Me![DepartmentID].Requery
    If lngDepartmentID <> 0 Then Me![DepartmentID] = lngDepartmentID

Exit_DepartmentID_DblClick:
    Exit Sub

Err_DepartmentID_DblClick:
    MsgBox Err.Description
    Resume Exit_DepartmentID_DblClick
End Sub

Open in new window

0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Hi Experts,
Can some one tell me what is this code doing (it's on a module).  The reason I'm asking is because I'm currently using access 2010 and with no problems but when I tried to open my database on access 2016 version, it  gave me this error message: The code in this project must be updated for use on 64-bit system, please review and update Declare Statements and mark them with the ptrsafe attribute".   I have no idea how to fix it also when I open the database in access 2016, I can't even open form view, it's gray out , does any one know why.  I'm using full version of access 2016.  

Thanks,



Option Compare Database
Option Explicit

'////////////////////////////////////////////////////////////////////
'Password masked inputbox
'Allows you to hide characters entered in a VBA Inputbox.
'
'Code written by Daniel Klann
'March 2003
'////////////////////////////////////////////////////////////////////

Private Declare Function CallNextHookEx Lib "user32" ( _
    ByVal hHook As Long, _
    ByVal ncode As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long

Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" ( _
    ByVal lpModuleName As String) As Long

Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" ( _
    ByVal idHook As Long, _
    ByVal lpfn As Long, _
    ByVal hmod As Long, _
    ByVal dwThreadId As Long) As Long

Private Declare Function UnhookWindowsHookEx Lib…
0
Is it possible to freeze multiple column in a report?
0
I want to update values in one table 'tblTaxRecs' with information from another table 'tblFees'

tblFees contains two fields 'BRT  Numeric Long Interger' and  'FeeAmount   Numeric Double'

The Pertinent fields in tblTaxRecs' are 'BRT numeric Long Integer', 'TaxYear  Numeric Long Interger', 'FeeAmount  Numeric Double', 'DateRevised Date', 'UserRevised Short TExt'.

For every record in tblTaxRecs with a taxyear of 2018 and matching a BRT in tblFees, I want to replace the 'FeeAmount' with the corresponding FeeAmount in tblFees that matches the BRT in tblTaxRecs.  If possible I also want to stamp each updated record in tblTaxRecs with the current date and time in field 'DateRevised' and the string 'Updt20190318'.
0
I need to do some work on a client's DB.  Despite the clients best efforts and repeated emails some user don't close the Access 2013 application when leaving for the day.  I did some research on forcing user out of the DB after some period of inactivity and came up with this solution on the Microsoft website.

Microsoft Suggestion For Forcing User Out of an Application

I am hesitant to use this one due to its use of 'TimerInterval'.  Somewhere along the line in my many years of Access development I remember being warned against using the 'TImerInterval' because in some way it could become problematic.  

Do any of you have experience with the having to remotely shutdown users due to inactivity?  DId you use the 'TImerInterval' function or are there better methods?
0
I have a form that allows a user's email address to be written to a separate table.  Here is the code:

Private Sub Form_DblClick(Cancel As Integer)

    If MsgBox("This function will write the selected contacts email address to the vendor recipients list.  Do you wish to continue??", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    Dim RS As DAO.Recordset
    Dim item As Integer
    Dim i As Variant
    Dim ctl As Object

    Set RS = CurrentDb.OpenRecordset("tblRecipients")

    With RS
            RS.AddNew
            RS!EstimateID = Forms!frmEstimates.txtEstimateID
            RS!VendorID = Forms!frmSelectVendorContacts.Form!frmSelectedVendorContacts.Form!txtVendorID
            RS!ContactID = Forms!frmSelectVendorContacts.Form!frmSelectedVendorContacts.Form!txtVendorContactID
            RS!ContactEmail = Forms!frmSelectVendorContacts.Form!frmSelectedVendorContacts.Form!txtEmailAddress
            RS.Update
    End With

    RS.Close
    Set RS = Nothing

    Else
    Exit Sub
    End If

End Sub

Open in new window


But if the user double-clicks another individual I want the code to enter both contact IDs and email addresses, or how ever many were double-clicked, into the ContactID and ContactEmail fields separated by a semi-colon and a space.  But trim the semi-colon off if no more are added.

How can this be done?
0
I need help with a function that will filter a two dimensional VBA array based on some criteria and store the filtered elements into a new array.
What I need would be clear from the code included.
As you would note I am running a loop to filter a startingArray and storing the matching elements in filteredArray.
The code I have posted works but in this case I was able to ReDim filteredArray(1 To 3, 1 To 3) As Variant because I knew how many elements I would get in the filtered array (in this case 3). In a real scenario I would not be able to do this because I would not know in advance in how many elements in my starting array would match the filtering criteria.
I would appreciate your help in the correct way of doing this.

Private Sub FilterTwoDimensionalArray()

Dim startingArray() As Variant
ReDim startingArray(1 To 6, 1 To 3)

Dim filteredArray() As Variant
ReDim filteredArray(1 To 3, 1 To 3) As Variant
' In the above line I am able to Redim filteredArray to (1 To 3, 1 To 3)
' because I know in advance how many elements filteredArray will need to accomodate
' as I can see the data


startingArray(1, 1) = 1
startingArray(1, 2) = 3
startingArray(1, 3) = "This is an apple."

startingArray(2, 1) = 4
startingArray(2, 2) = 9
startingArray(2, 3) = "He looked happy."

startingArray(3, 1) = 10
startingArray(3, 2) = 12
startingArray(3, 3) = "This is an apple."

startingArray(4, 1) = 13
startingArray(4, 2) = 16
startingArray(4, 3) = "This is a good 

Open in new window

0

Microsoft Access

220K

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.