Microsoft Access

221K

Solutions

311

Content

i
The collection of articles, videos, and posts on this topic.

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

I'm running MS Access 2016, and I'm looking for a function that will do the following when closing a form in my database.

1. After active form closes, check if there are any other open forms
2. If there are no other open forms, fire a command such as 
MsgBox "All forms have been closed."

Open in new window


I appreciate any expert advice you can provide! =)
0
3 Comments
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Hi

I have an Access form with a combobox that has a button next to it where the user can add
more information to a table that is the data source of that combobox.When the user closes the form
that was opened with the button I want the combobox to automatically be refreshed with the additional data
Do I requery the combobox on the other form? or do I use some way to requery the combobox when the user goes back to it with their mouse?
0
9 Comments
Sorry again people there!

I'm now struggling to to put the print view back and be able to close the reports , I'm trying to use the two codes below:

<group id="grpPrint" label="Print" visible="true">
                    <button id="PrntRpt" size="large" label="Print" imageMso="PrintDialogAccess" onAction="=PrintDialog()"/>
                </group>
 
                <group id="grpClgrpRprt2" label="Close">
                     <button id="PPrClose"  label="Close" imageMso="PrintPreviewClose" size="large" onAction="OnCloseReport"/>
                </group>

Open in new window


The reasons why I want to use this method is because Ms Access application does allow users to export report to excel if the USER DOES NOT HAVE full version Ms access , this happen for those using runtime

The export function is working well now runtime but the two are give an error as MACROS FUNCTION does not exist.

Regards

Chris

print-view.png
0
1 Comment
Hi

We need to calculate the final payroll Journal, unfortunately the figures for PAYE has to come from the extensive and complicate cumulative basis calculations. Now all I want here is to find out the following:

(1)      How to put the Salary journal report consolidating all the PAYE by staff in the report footer
(2)      Hide the detail part which shows calculation by staff, so that when printing only the staff on the report footer should show.
(3)      The details part of the report will have all the calculations for the staff to supply the footer report or act as the recordset but must be hidden so that only the report footer shows only.

Regards

Chris
Payrollsummary.png
0
4 Comments
Hi

I can't see a way to change the form caption shown in the following image. Thanks

Image1.JPG
0
2 Comments
Hi,
I would like to put custom tool bar in my Ms-Access application.  Not the predefined tool bar, but one that I can customize for an accounting application I am doing:
Something like this:

SALES                         PURCHASES               RECEIPTS                     PAYMENTS
Cash Sales                 Local Purch.               Cash Rec.                    Cash Payments
Credit Sales               Foreign Purch.           Linx Rec.                     Cheque Payments
Other Sales


The titles will be the headings... and the items below will be drop-down list that will appear when click on the heading.
Thank you.
0
4 Comments
Is there a way to conditionally format a whole row on a datasheet where "Tech" is the value of a field named TypeOfAppt? I want to make the back color yellow if TypeOfAppt is "Tech".
0
11 Comments
I am setting up a procedure to email notifications in my Access 2013 application.

The client wants to send a list of pending payment to each of their shippers.  There will be many shippers and each shipper will get and email containing only their pending payments.

I have the logic set up using sendobject and it works just like it was designed to.  

DoCmd.SendObject acSendReport, _
                 emailRpt, _
                 acFormatRTF, _
                 eMailTo, _
                 eMailCC, _
                 eMailBCC, _
                 eMailSubject, _
                 eMailBody, _
                 False

Open in new window


The issue is that every time an email is generated for a shipper this message appears on the screen and the user has to click 'Allow'.  

This get very repetitive and time consuming when there are dozens of emails being generated.

Email COnfirmation Message
Is there any way to keep this message from coming up for each email?
0
8 Comments
Latest Windows Security Patch Crashes Microsoft Access 2013/2016 applications with "The OpenForm action was canceled". This started happening Nov 12 2019. I have had at least 14 client computers over the last 2 days call with this issue. So far my only solution has been to do a system restore and pause their Windows updates for the time being.
It also appears that customers with Windows 1809, end of Service was scheduled for Nov 12 2019. and apparently some older Home versions would not allow you to pause updates.
I found an article that suggested that registry settings for MSCOMCTL.OCX are messed up by the update with recommendation to unregister and re-register the control:
using Windows Power Shell:  regsvr32 -u "C:\Windows\SysWOW64\MSCOMCTL.OCX"   and then  regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
Although the commands are successful, that does not solve my problem. This problem is occurring for both Windows 10 and Windows 7 computers (I would assume 8.1 as well)
0
4 Comments
Hi Experts,
I have a question about query.  it was just happened.  I was trying to run an update query and this query I have been using for a long time and never have any problems but today when I try to update a record#, it gave me an error message "Query " is corrupt" when I click help, it says "You have referenced an undefined query name, check the queries listed in the QueryDefs collection to make sure you reference a valid query name".  I even create a new update query, it gave me the same error.  does anyone know why and how to fix?

Thanks
0
4 Comments
PMI ACP® Project Management
LVL 19
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Start Today!
I have a following string data:
[aaaa][bbbb]ccccccccc
or
[aa][bb][cc]dddddd
or
[a][b][c][d]eeeee

I would like to extract ccccccccc and dddddd and eeeee
do you know how to do with single query?
0
10 Comments
Dear expert team

I I have two date field, one is registration date and one is exam_date. I need to fill data in a field called work_date field as follow:

 

1)      If registration date = exam date, then Exam date to be entered in work date as date value

2)      if exam date is next day from registration date, then put registration date in work date

3)      if registration date is same as exam date, but between the hours 12 am to  4:am, then put the date before registration date

4)      if exam date more than one day off, follow same system above

Please help, I attached the database
Database2.accdb
0
7 Comments
I am modifying a procedure for a custom Query By Form. The original procedure searches multiple fields in a table, but I am modifying it to search only single field in a table

The modified code is in the click event of the Search - Test 1 button on frmMainMenu. You can view the original unmodified procedure in the click event of the Search - Test 1 (All Orig. Code).

Here are the text box names:

txtDateFrom -- Date From
txtDateTo -- Date To

Here is the modified code:
' First, validate the dates
    ' If there's something in Date From
    If Not IsNothing(Me.txtDateFrom) Then
        ' First, make sure it's a valid date
        If Not IsDate(Format(Me.txtDateFrom, "mm/dd/yyyy")) Then
            ' Nope, warn them and bail
            MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
            Exit Sub
        End If
        ' Now see if they specified a "to" date
        If Not IsNothing(Me.txtDateTo) Then
            ' First, make sure it's a valid date
            If Not IsDate(Format(Me.txtDateTo, "mm/dd/yyyy")) Then
                ' Nope, warn them and bail
                MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
                Exit Sub
            End If
            ' Got two dates, now make sure "to" is >= "from"
            If Format(Me.txtDateTo, "mm/dd/yyyy") < _
                Format(Me.txtDateFrom, "mm/dd/yyyy") Then
                MsgBox "Contact To date must be greater

Open in new window

…
0
13 Comments
Need help with overcoming 3035- system resources exceeded error carrying out an INSERT sql statement.

Please note image attachment that shows error message - (along with value of what sEntireLineItem is) and following code:
The yellow highlight should be on Currentdb.Execute sSQL

      sSQL = "INSERT INTO [usgaapfileswtags] ([tagID], [usgaapfilesid], [usgaaprow], [usgaaplineno], [usgaapvalid]) VALUES (" & lTagID & ", " & lIDFileName & ", '" & sEntireLineItem & "', " & lCounterTag & ", " & bValidTag & ")"
      CurrentDb.Execute sSQL
      DoEvents

Open in new window


So obviously the sql INSERT statement will be pretty long

I already do this earlier in the code:

If Len(sEntireLineItem) > 65500 Then
        sEntireLineItem = Left(sEntireLineItem, 65500)
        
      End If

Open in new window


And I do have usgaaprow as Long Text as a data type.  I did check all the other values are quite small and match without a problem.  I know it's the variable: sEntireLineItem

Here is the value of sEntireLineItem string itself:
  <us-gaap:BusinessCombinationDisclosureTextBlock contextRef=''FD2018Q4YTD'' id=''Fact-82E23980F0AD591486D298F5A3EA3736''>&lt;div style=''font-family:Times New Roman;font-size:10pt;''&gt;&lt;div style=''line-height:120%;padding-top:18px;font-size:10pt;''&gt;&lt;font style=''font-family:inherit;font-size:10pt;font-weight:bold;''&gt;ACQUISITIONS, GOODWILL, AND ACQUIRED INTANGIBLE ASSETS&lt;/font&gt;&lt;/div&gt;&lt;div

Open in new window

…
0
9 Comments
Hi

I have an Access form where different images are loaded to a control. Is there a way to stretch he image to a larger size that looks the same no matter what size the original image is?

Thanks
0
4 Comments
Values to be identified as duplicates will be in the second (B) column (with header). I will need to identify rows with duplicate values in that column, but preserve all values in the other columns of that row.

It would be good to insert the string, "Duplicate" (without quotes) in the column to the far left of the duplicates other than the 1st instance. For the 1st instance, mark as "First Instance", would be desirable. Then I can sort on that column. Non-duplicates should not be marked at all.

Spreadsheet has 75,000 rows.

Using an Inline Function would probably be preferred if possible, rather than a nested VBA loop type macro. But please do what you think best. I'm unable to find a reasonable solution searching the web. -- BTW:  CONDITIONAL FORMATTING hangs the program with too many ROWS.

Sample Content: (sorry, but the "Indent" tags are not working). The point is that the values in other columns of those rows do not affect the logic.
Column A is Blank

Column-B-Title   Column-C-Title   Other-Columns-w-Title
5555
Some-Value
5555
Some-Other
5555
Not Important
3333
Some
3333
Thing
XXXXXX
xyz
XXXXXX
abc
Anything
pqr
Else
stu

(Note: Sorry about all the edits. Couldn't find a "Preview" button prior to publishing. Not familiar with this new interface.)
0
2 Comments
I have a table named tblPotentialAppointmentDatesAndTimes.  In that table are three fields...  AppointmentID (the primary key) and Date and Time.  When I click a button on a form I want the table to fill with records based on the number of doctors in another table named tblDoctors.  The field have to populate with a date/time every 15 minutes starting at 08:00 am and going through 05:00 pm

In other words like this:

 Date Time Example
0
4 Comments
trying to update a record in sql server
getting error message when a field is blank  ( = null)


These are unbound  textboxes on a form.

R.Edit
' 13 FIELDS SKU INFORMATION
      R![SKU] = Nz(Me("SKU_FRM"), "") ' SKU
      R![QTY] = Nz(Me("QTY_FRM"), "") ' QTY
      R![TARGET_PRICE] = Nz(Me("TARGET_PRICE_frm"), "") ' TARGET_PRICE
      R![TARGET_GP] = Nz(Me("TARGET_GP_FRM"), "") ' TARGET_GP
      R![CURRENT_PRICE] = Nz(Me("CURRENT_PRICE_FRM"), "") ' CURRENT PRICE
      R![CURRENT_GP] = Nz(Me("CURRENT_GP_FRM"), "") 'CURRENT_GP
      R![VENDOR_GUIDELINE_GP] = Nz(Me("VENDOR_GUIDELINE_GP_FRM"), "") ' FLOOR_GP
      R![PRODUCT_DESCRIPTION] = Nz(Me("PRODUCT_DESCRIPTION_FRM"), "") 'PRODUCT_DESCRIPTION
      R![TARIFF_ITEM] = Nz(Me("TARIFF_ITEM_FRM"), "") '  TARIFF ITEM
      R![RENEWAL_SKU] = Nz(Me("RENEWAL_SKU_FRM"), "")  '  RENEWAL SKU
      R![RENEWAL_OLVP_QUOTENUMBER] = Nz(Me("RENEWAL_OLVP_QUOTENUMBER_FRM"), "") '  RENEWAL OLVP ITEM
      R![APPROVED_PRICE] = Nz(Me("APPROVED_PRICE_FRM"), "") ' APPROVED PRICE                                                                             error
      R![APPROVED_GP] = Nz(Me("APPROVED_GP_FRM"), "") ' APROVED GP                                                                                               error

R.Update

Open in new window


i thought  NZ would take care of a blank value ?


These 2 fields may not have data in the textbox,
but i still need the code to finish the .update

Thanks
fordraiders
0
7 Comments
Hi

I have a sub form that needs to filter data based on a label in my main form. The label contains a key value
How and where would I do this?

Thanks
0
6 Comments
CompTIA Cloud+
LVL 19
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Learn More!
How to modify the following query to add a total column of TaskSelected?

TRANSFORM COUNT(T.TaskSelected)
SELECT R.TAID, T.TaskSelected
FROM RBWS R
LEFT JOIN TaskSelected T  ON R.TaskSelectedID=T.ID
GROUP BY R.TAID
PIVOT T.TaskSelected
0
2 Comments
I have a field in my table bm_ID that I need to query and filter. I'm unsure how to pull it out but if the string has 2019 as such '3E: 100820191245 | B' or '3E: 3Q19'  or '3E: 20190927093335' or '3E:093020190116' then I need to pull it out of the table for my report but again, I'm not quite sure how I can accomplish this...Any suggestions and examples would be greatly appreciated in accomplishing this...Thanks...
0
3 Comments
Hi
(1)      I have a simple select query below, now what I want is to have the cumulative balances by employee under cumtotal
(2)      A moving value difference which is achieved by subtracting the cumulative value in the month of Feb by Jan, example see below:
                 
     
Emp      Mth       Sal          Cum      Diff
James      Jan      5,000      5,000      0
              Feb  5,000      10,000      5,000
Beth      Jan      4,500      4,500      0
              Feb  4,500      9,000      4,500
Annie      Jan      3,500      3,500      0
              Feb  3,500      7,000      3,500
Chris      Jan      3,650      3,650      0
              Feb      3,650      7,300      7,300

I do not want to use the domain function, I have enough of poor performance, I understand the answer to this are sub queries, this is where I’m trapped, I real need your help. Our pay as you earn works on cumulative basis, that is the reasons why I need this.

I will appreciate if can be done, unfortunately access does not work like excel, otherwise this is very thing in excel.

See actual query:

SELECT tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, Sum(([tblSalaries].[Gross])) AS CumTotal, 0 AS Diff
FROM (tblstaff INNER JOIN tblpayslips ON tblstaff.Id = tblpayslips.EmployeeName) INNER JOIN tblSalaries ON tblstaff.Id = tblSalaries.Id
GROUP BY tblstaff.Id, tblstaff.FirstName, tblstaff.LastName, tblstaff.JobTitle, tblpayslips.SalaryDate, tblSalaries.Gross, 0
ORDER BY tblstaff.Id, tblpayslips.SalaryDate;

Open in new window



payrollcum.png
Regards

Chris
FinTest.accdb
0
17 Comments
I have an Access DB, running on an office PC.
I'd like have a webpage that displays data from this DB.
I have a shared drive that can be used so others can see this webpage - without directly addressing Access.
This page will be designed as a dashboard, and will have a wide variety of links, tables, charts.
How do I setup a connection & display the data?
Thank you in advance.
Russ
0
7 Comments
I have two queries, contain_keyword, and doesNotContain_keyword, by using like and not like in criteria.
total record is 149, contain_keyword shows 115, and doesNotContain_keyword shows 5

149 /= 115 + 5

do you know why the query: "contain_keyword" does not show all records?

Database1.accdb
0
7 Comments
As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.

Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:

1.      First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2.      Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx

I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)

Private Sub LinkSpreadSheets()

Dim Fpath As String, _
  XLname1 As String, _
  tb1 As String, _
  tb2 As String, _
  tb3 As String, _
  tb4 As String

Fpath = Environ("USERPROFILE") & "\Documents\databases"

XLname1 = "\ExcelFile.xlsx"
                   
tb1 = "1001"
tb2 = "2001"
tb3 = "3001"
tb4 = "F008"

With DoCmd

    .DeleteObject acTable, "1001"
    .DeleteObject acTable, "2001"
    .DeleteObject acTable, "3001"
    .DeleteObject acTable, "F008"

    .TransferSpreadsheet acLink, , tb1,

Open in new window

…
0
6 Comments

Microsoft Access

221K

Solutions

311

Content

i
The collection of articles, videos, and posts on this topic.

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.