[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 following table in Ms. Access.
CHID      CDATE      MACHINENO
1      1-APR-18      1
2      1-APR-18      2
3      2-APR-18      2
4      3-APR-18      1
I need following returns
DATE
MACHINE NO      MAX OF CHID
1-APR-18      1      1
1-APR-18      2      2
2-APR-18      1      1
2-APR-18      2      3
3-APR-18      1      4
3-APR-18      2      3
Prompt response will be appreciated.
0
I have the following query:
SELECT tMain.TrayNo, tMain.LensSupplierNo
FROM tMain
WHERE (((tMain.LensSupplierNo)="Tx123"));

I would like to displays this result in a message box... and I would like to have use VBA instead of a saved query.  Is this possible in some way?

EX: MSGBOX "Stuff found: " & SELECT tMain.TrayNo, tMain.LensSupplierNo FROM tMain WHERE (((tMain.LensSupplierNo)="Tx123"));

Or maybe put the results from the query into a variable somehow to use in with the message box?
0
Have a MySQL DB on a Word Press web site that I need to get data from.

I'm trying to figure out what the best way is to move the data based on what I have to work with.   I figure I can either:

Push data from the web site when a new entry is made in a table or on a set schedule

OR

Pull the data from the web site to the local system.

What form that takes is what I'm not sure about.   Some of the data may be un-encrypted so I want a secure form of data transfer.

What I have to work with:

The web site has the capability of running cron jobs and I have full access to everything, although I don't believe I can do remote DB access (plus it would not be secure).  On the local system, I'm working with Microsoft Access and have SFTP and SSH access to the web site.   On the local server, I can get pretty much anything done (ie. setup a SFTP server), but nothing is in place at the moment.

Push would be my preference for efficiency, but I think it will have to be a pull from the local system.

Seems like DB to DB is out, so I'm thinking of a DB trigger or a cron job that would write the data to CSV file(s), then delete the data from the DB.  Then from the local system, every xx minutes, do a SFTP session and grab any files.

The web site by the way is a WordPress site, so if there are any plug-ins that deal with data transfers along these lines, that is an option as well.

Any thought's or ideas?

Jim.
0
I have an access database that I've imported certain information from our ERP system and it makes a new table.  Once the table is created, it runs a macro every time you start the database and appends any new records to the existing table.  Is there an easy way to create a macro that would update the existing information if something has changed in the ERP system and it sees that a record in the access database doesn't match up and needs to be updated?  For example,  their address changed from 1234 State Street to 4567 Sunny Street.
0
Hi everyone!
I am having trouble to auto resize my navigation form in MS Access.
I've used the code from Access Developers Handbook and it works perfectly when I apply it to a single form. However, when I try to resize the same form in my navigation form the code is not working.
I get the feeling the Access Developers Handbook code is working but not on navigation subform. It only needs a minor adjustment and I can't really get into the code that deep to understand what's happening. I'm sure that someone with a bit more knowledge than me would know how to do it. The code is in attach.
I would really appreciate if you could help me on this as I'm beginning to be very frustrated.

Thanks in advance!
0
I have a question on how to calculate pooled standard deviation in MS Access. I know MS Access can use StDev(table.field) to get standard deviation. but how can I get pooled standard deviation such as subgroup size is 5? Take the following data as example, in table, there is a field "Measure" as following, set subgroup size as 5
Measure      subgroup
149.5308      1
149.5332      1
149.5288      1
149.5296      1
149.529              1
149.5323      2
149.5301      2
149.5317      2
149.5295      2
149.5328      2
149.5324      3
149.536              3
149.5306      3
149.541              3
149.5416      3
with measurement continue, the data will be continuously recorded. How to get pooled standard deviation so that I can get Cpk? Thanks.
0
I need code that will allow me to programmatically select the printer to which an access report prints.

 I have both Win10 and XP computers on our network.  When we create a pick ticket, we always want the report to print on our SHIPPING printer.

To do this we have the SHIPPING printer plugged into our LAN in our shipping department, and we installed in as a shared printer on one of our servers.  

To make things work we first go to the machine we use to create the source code, and install the shipping printer.  XP gives the printer the name: SHIPPING on OurFileServer.

Next we open the source code with Access 2K, open the report, select page set up, page, select specific printer, and then select \\OurFileServer\SHIPPING from the drop down menu.

When compiled, the code works on all of our XP machines, but not our Win10 machines.

When we install the shipping printer on our Win10 machines everything installs as expected, however, the name is simply SHIPPING.  We can select the printer using WORD, and it works fine, proving its properly connected.  When we try to print our report in Access, however, Access can not find the printer and recommends using the default printer instead.

My guess is that it has something to do with the fact that the printer has a different name in the printer control panel, (SHIPPING vs SHIPPING on OurFileServer), but I may be all wrong.  On solution would be to use code to select the printer.   Does anyone have any suggestions?

0
I using the following code and get an error message -
You tried to execute a query that does not include the specified expression  as part of an aggregate function. (Error 3122)

UPDATE tblLastFileDate INNER JOIN tblConsolidatedHistory ON tblLastFileDate.[Last File Date] = tblConsolidatedHistory.DateMonth SET tblLastFileDate.[File Name] = First([tblConsolidatedHistory].[FileName]);

Open in new window


Thanks
0
This should be a piece of cake for Access developers.

I'm running a Do Loop (rs.EOF) where a table is set as the recordset.  The Table could include duplicate Id numbers across several records and in such case, I need to insert a value in the "Occurrence" field counting the occurrence.

For example:
Id          Occurrence
1                    1
2                    1
2                    2
2                    3
3                    1
0
POS systems are widely used in supermarkets. All the POS in a supermarket should be connected to the POS center which is accessed to EFD system.
The POS system should be able to capture the important data from the electronic fiscal device (EFD) as follows:
(1)      EFD Invoice Number  ( String) in Ms access database invoice form in control called TaxInvoiceNumber
(2)      EFD Signature (picture) to be captured at the end of the invoice report
(3)      EFD date same as above
(4)      EFD Time same as above
Now my question is that is there a way we can use an API code to help us capture the above date from a window based application called EFD?????

Kindly see how you can help us on this, the above software is used for tax purpose in many countries. I will appreciate your help regarding the above issue.

Regards

Chris
0
I am creating on fly  “Where condition for a query .
I am using PatHartman  example:
*****************************************
Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc
    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub

Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
   
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWHERE = …
0
Please see attached Access Database file. Something went wrong and now it is not printing correctly. It is picking multiple data from the sub form. What I want is when I click on Print DC button on the frmDCEntry form, it should only print data related to that particular record. Pl suggest and help. I am new to developing Access applications.

Thanks.
My info
Invoicing4.accdb
0
Anyone can help me perfect the Me.txt&Ibox&J = &I “v” &J

Dim I, J As Integer
      For I = 1 To 2
            For J = 1 To 5
       
                  Me.txt&Ibox&J = &I “v” &J

            Next J
      Next I

Example      :      Me.txt1box1 = 1v1
Example      :      Me.txt1box3 = 1v3
Example      :      Me.txt2box1 = 2v1
Example      :      Me.txt2box5 = 2v5

At the unbound box txt1box1, it will show 1v1 as output
0
Microsoft Access 2013 Database
I have a table (tblDieMods) which has the modification name e.g. ABCE-123 in the field [ModFullID] and a hyperlink to an external pdf file in the field [ModSheet]

In my form , I have a list box which shows all of the modification names. (Query in List Box has both [ModSheet] as column 1 set to 0 width and [ModFullID] as column 2 set to 5mm width so only [ModFullID] can be seen.

I want to be able to select a modification name [FullModID] from the list box, then click a button next to it to follow the corresponding hyperlink [ModSheet]

Any easy option or bit of code to make this so?
I'm totally new to vba so any help would be great!
0
I am trying to use a delete query in Access 2013.  I have done it a million times before in other versions.  If I put in my criteria "Ohio", Up pops all the Ohio data.  If I put <>"Ohio" I get nothing, Not In("Ohio"), or Not In('Ohio') still nothing.  Same results for a select query.  The field is Short text 256 Characters.  Any help would be appreciated.  Yes there is other data.
0
ms access 2k10+ report outputted in html format. Can't put a jpg image in the page header section and have it carry into
the report when run. Have tried design and layout views, logo icon, etc... the image appears in the design but does not
carry into the created htm report
0
This is driving me crazy. Does anyone know what the rules are for accessing a field from a report recordset that you do NOT place in the report design? Some times I have no problems, other times I get an End / Debug saying it can't find the field.

Example: I have a record source for a report that has a field called ActionPlanDate. It's in the data, but not on the report layout. I have a line of VBA code that states:
txtResponseDate1 = ActionPlanDate

I need to do it this way to not clutter the report with controls.

I'm getting the message... Microsoft Access can't find the field ActionPlanDate. It's seems to not be able to find any of the dataset fields that are not on the report layout. Even if I try Me.ActionPlanDate or Me.RecordSet.ActionPlanDate. None of this seems to work.

What's the rule?

Any help would be greatly appreciated.
0
I have a table that is not showing when I open an Access file.  I didn't delete it, so I know that it is there.  How do I show all tables?
0
How do I delete a table in Access 2016?
0
I need to convert an Excel 2016 file into an Access 2016 file.  The Excel file has 4 colums and multiple rows and 2 sheets.  Is there a way I can import this information into Access and have it converted into a database file without having to retype all the information?
0
Hi
singleparentmeet.com is a really pitiful dating site. I want to unenroll, but their site is truly pathetic.
They don't have tech support and their help page requires 3rd party cookies.
Dating is a hotbed of scammers. Enabling 3rd party cookies doesn't appeal to me, especially from a crappy site that might have vultures, or Nigerians waiting for idiots to allow them access to info. Nigerian vultures!? How can I safely unenroll from this  site? Can I get VISA to read them the riot act? They will probably charge me soon, month end!

Thanks
0
I am using the code below that I found online to delete records from a table, transfer new records into that table and then update data in a different table.  It prompts the user for the Excel file that needs to be transferred into the Access table for these operations.  Since this comes from a different department at different times the file name has a date as part of the name.  I didn’t have a problem with this until they added a password.  Now for the code to work the user has to open the Excel file first to enter the password and leave the file open.  Then they have to select the Excel file again when they are prompted from the code.  I don’t want the user to have to do this twice but don’t know what code to add or where to add it to make the file open and then continue with the transfer.  I would appreciate any help you can offer.

Private Sub cmdImport_Click()

Dim strXls As String
strXls = "\\corpfs03\SHARED\hcf&um\SHARED\Weight Watchers Database\" _
& "Weight Watchers - Reimbursements\Check Templates for AP\Current Year\"

    SendKeys "Y"
    SendKeys "Y"
    DoCmd.OpenQuery "qryDeleteChecksReceived"

    strXls = XLSFilePicker
    If strXls = "" Then
       MsgBox "Operation cancelled"
       Exit Sub
    End If
DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
    strXls, True
       
    SendKeys "Y"
    SendKeys "Y"
    DoCmd.OpenQuery "qryUpdateChecks"
   
End Sub

Function XLSFilePicker(Optional strPath As String) As …
0
I am creating on fly  “Where condition for a query .
I am using PatHartman  example:
*****************************************
Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc
    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub

Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    strWHERE = ""
   
    If Me.txtFirstName & "" = "" Then
    Else
        strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWHERE = …
0
A few of the dropdowns in Access database do not show all of the information they need to pick from in the first example.  At the top it says "Type" and has a reference of 174 that nobody knows where that comes from.  It should have "DLR, OEM, HYD" to choose from.  The second one says "Terms" with C.O.D. and the drop down has no more to choose from such as Cash in Advance, Credit Card, Net 30 days, etc.  Please refer to the image in the attachment:
Access-Errors-1.pdf
0
I'm trying to use an Access updated query to extract the date from a field in the following formats:
#241 QIC Demo Merged Report Reopening 04-11-2018.xlsx
#003 QIC DEMO Merged Report 04-05-2016 REOPENING LOGS.xlsx
#118 QIC Demo Merged Report_08-18-2016 REOPENING LOGS.xlsx
#039 QIC Demo Merged Report 01.13.2017-Remand(1 of 2 ).xlsx
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.