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

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
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
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
I'm experiencing a rather bazar issue I've never run into previously.

I'm inserting data into an Access 2003 text field (255 char) where several rows are null or blank but when I test the length len([Field]), they all come out as over 100 characters wide.

The source of this field (from a customer) is a SQL Server table with the same characteristic.


How can I transfer this field and end up in Access with a zero length value?
0
I have created Five action queries in Ms Access 2016 for inserting data that require further manipulation to give correct information , one of the INSERT QUERY see below:

INSERT INTO tblPosAccounts ( QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.QtySold, tblPosLineDetails.UnitCost, tblPosLineDetails.CostOfSales, tblPosLineDetails.ItemSoldID, tblPosLineDetails.POSDate, tblPosLineDetails.CosAcc, tblPosLineDetails.BSIDCos
FROM tblPosLineDetails;

The above query works very well if used against Ms Access 2016 table BUT fail to append in MS SQL Server 2016, kindly assist me on this all the links are just super no errors whatsoever. I do not understand this. The SQL Server has the same tables as MS Access.

See what you can do.

Regards

Chris
0
running access 2016 as part of office 365. Now trying to run an existing access app that requires ms office xx.x object library and its no where to be found in references list.
How do I get an object library?
0
For the point of sales app I want to use the input box because for Tax purpose because the tax rate keep on changing year in year out , what I want to do is to hard code the txttax control to be only receiving percentages not absolute values , how do I do it? For example I want to be seeing rates like 10% , 16% etc whatever is applicable not values like 10,16 etc.

Private Sub ProductID_AfterUpdate()
Dim s As String
s = InputBox("Tax Value required", "Internal Audit Manager ", "Tax Percentange")
Me.txtTax = s
End Sub

Regards

Chris
0
I have a form that has a list box – there are two queries that serve as the data source for the list box
The users can switch between the data sources by clicking on an option on the form
Above the list box there are label fields that act as column headers and sort the data displayed in the list box
The first query works with no issues, the user can sort the data in the list and apply various filters
The second query has no additional filter options but I do want the users to be able to sort the list by the same options as the 1st query
The sort options work on all the columns of the second query except the ‘product description’ field.
The ‘product description’ field is the only long text field in the queries
The only difference between the queries is the 2nd used to exclude records from the 2nd query.
When you click on the ‘product description’ sort button it does change the order of the data but does not sort it correctly, if it is somehow sorted by some other field it’s not obvious, it does arrange the data differently ascending vs descending and they are always in the same order.
I’ve tested the 2nd query by adding the sort option on the product description in the query itself – it doesn’t work there either.  It seems to just be the combination of the long text field & the no records in the 2nd table.
Can anyone tell me why the sort on the 2nd query / ‘product description’ doesn’t work and how to fix it?

Here’s the code:

1st table -- tblProducts      
RecID      …
0
In Access 2010, I have a form where I'm updating records. Every time I update "field1", I'd like to automatically change the value in "field2" to 'complete.'

I appreciate any feedback as to what the most efficient method is for this task. I won't be able to test any responses until Monday. Thanks in advance!
0
I'm working in MS-Access 2010. I have a form based upon the table name "tblData." When the form loads, I'd like to only display records that have a "fldResults" text value of "Incomplete."

I'd also like to add a button on the form that can toggle back and forth between displaying all records from "tblData" and just records with "fldResults" text value of "Incomplete."

I'd love the minimize the amount of VBA that would be needed to accomplish this, but if a VBA sub routine is the best approach, I appreciate any recommendations. I won't be able to test any replies until Monday. Thanks in advance for any help! =)
0
Access 2010
Linked sql server tables

Let me start by saying this question has nothing to do with duplicate data.
DATETIME fields repeating data for entries.


I have something strange that started recently.
When I view the Linked tables in Access.
It displays the data : as shown below...for a repeated  ID

repeated rows of data
in sql server  this is correct...( second entry)

sql server entry
not so concerned about the military time..but...the entries in Access are repeating...
if i had 5 entries..
they would all be the same ?

or maybe military time is causing the problem ?




Thanks
fordraiders
0
If I have an Access 2016 table that has the following layout

PKID | Month1 | Month2 | Month3 | Month4 | Month5 |etc...
___________________________________________________________
1       |     100     |                |     70       |                |                |
___________________________________________________________
2       |     28       |        3      |                |     25       |                |
___________________________________________________________
3       |                |                |                |                |       8        |
___________________________________________________________


Is there a way to write a query or code that outputs to a table to transpose the values along with the field name into something that looks like this:

PKID |  Month  | Value     |
___________________________
1       | Month1 |    100      |
____________________________
1       | Month3 |     70       |
____________________________
2       | Month1 |    28        |
____________________________
2       | Month2 |    3           |
____________________________
2       | Month4 |    25         |
____________________________
3       | Month5 |     8         |
____________________________

It doesn't have to be a query.  Whatever is the best solution, works for me...
0
I need a working example to extract an i address from a string using regular expressions.
Thanks
0
Hello Team

I have the following simple sql code within MS Access

SELECT IIf(IsNull([Total]),"Total","Total") AS [DAY], Sum(qrySLBMECom.Total) AS Total
FROM qrySLBMECom;

 What I require is for the results to show a zero value when  'Sum(qrySLBMECom.Total) AS Total' is null.  

I have tried applying IIF(IsNull....etc but I am receiving an error when including these functions.

Thanks in advance.

Dale
0
Where do I go wrong on the insert  code below?
Private Sub CmdPos_Click()
Dim db As DAO.Database
Dim strSql As String
strSql = "INSERT INTO tblPostClearance (" & "tblPostClearance.Debit, " & " tblPostClearance.Credit " & ")VALUES(" & Me.txtCashReceivable & ", " & Me.txtCashReceivable & ")"
db.Execute strSql
End Sub
0
Hi Experts,

I am in need of some help in order to convince manager to agree on designing a table in a normalized manner.

Attaching two versions of the form, the first one is a none normalized version, while the second is in normalize state.
Also attaching how table definitions would be according to each of those forms.

Basically would need a list of advantages vs. disadvantages we are to expect by choosing each path, in this case in particular.

Since users are used to enter data manually in sheets in a weekly bases (similar look to the denormalized version) they have preference on taking that route.

FYI-Table in question is PatientsMedications (posting test data).

Thanks
Untitled.png
Untitled1.png
Untitled2.png
Untitled3.png
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.