Microsoft Access

221K

Solutions

52K

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

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
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
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
Hi Experts,
I had access 2007 covert to access 2010 many years ago and we still use the custom report tool bar from the previous version.  How to I find out what is in the custom report tool bar or when the custom report tool bar hide?  I went to Option and there is not report tool bar(this is the custom report tool bar we named) because we going to upgrade to access 2016 soon and I want to see the what we add on the custom report tool bar.

Thanks
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
This question relates to previous question...
https://www.experts-exchange.com/questions/29139086/VB-code-to-modify-records-associated-with-another.html

I tried to add a further comment that I hoped would re-open it but it does not seem to do that - sorry.

So my followup question is ......
The solution code given in the original post works fine and sets the required flag in the record.  However, I do also need to write some text into a field in the record   (CommentsNotSent) taken from a field on the form (CancellationText).

My code currently looks like this (and works fine) ....
With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                If vbYes = MsgBox("Would you like to write off this invoice ...." & vbCrLf & vbCrLf _
                & "Number - " & ![Invoice ID] & vbCrLf _
                & "Date - " & ![InvoiceDate] & vbCrLf _
                & "Type - " & ![InvoiceType] & vbCrLf _
                & "Description - " & ![Item_1_Description] & vbCrLf _
                & "Amount - £" & ![Total_Amount], vbQuestion Or vbYesNo, "Write off invoice") Then
                    .Edit
                    ![WrittenOff] = True
                    ![CommentsNotSent] =  [CancellationText]
                    .Update
                End If
                .MoveNext
            Loop
        End If
    End With

However, the problem is that the field CommentsNotSent may already contain some text and the new text needs to be …
0
I'm trying to use Access VBA to download files from the internet using a url. I need to send a click to the Save AS link on "What Do You Want To Do" dialog popup (see attached) . The file needs to be saved to a folder. I'm using IE 11. Thanks
Capture.JPG
0
A client recently reported getting this message as they try to print from a Access 2013 application I installed for them.  The message come up when they are trying to print a client account statement from the application.

Background: This application has been installed and running without issue for over a year.  I have made no application changes in that time period.  They just started getting the missing reference message last week. They sent me a snapshot of the message. Reference Error Message
It look like 'HSOUTLOLB' but I did a search on that and nothing came up.  

My first thought is that they changed something in their operating envioronment but I just wondered if any EE'ers might have a better knowledge of what might cause this issue.
0

Microsoft Access

221K

Solutions

52K

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.