Microsoft Access

221K

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 want to convert the First Name Last Name field to Last Name, First Name. For Example:
Michael Smith convert to Smith, Michael. Please note there is a space after the comma.
0
I have a form that I want to used a tab control for.  I have put the tab control on the form and can add, delete and edit information on the tabs but getting to actually work is another matter.  First what section of the form do you put the tab control in: Page Header, Detail, where?  I currently have it in the detail section where I think it should go.  Second question is how to you create the form so that the form knows what is on page one and what is on page two?   Do you repeat the tab control on page two so that you can get back to page one?  All the information on both pages comes from one table.  I have the form setup in sections so it is visually pleasing.  To get it to work I suppose I have to setup event procedures to go to the tab selected.
0
Is it possible to use a subquery in the SELECT statment in MS Access?

I am trying to
e.g.
SELECT
  SalesOrderNumber,
  SubTotal,
  OrderDate,
  (
      SELECT SUM(LineTotal)
      FROM Sales.SalesOrderDetail d
      WHERE d.SalesOrderID = h.SalesOrderID
    ) AS LineTotals
FROM
  Sales.SalesOrderHeader h;

Open in new window



Thanks in advance
0
Hi Experts,
I'm going to start working on to add a signature in a report, but I don't know where to start it and need some assistance. Here are some questions:
1). Is better to store the signature in the table (create a text field in the table) or store the signature in the same folder as the database?

2). If I store the signature in the table, on the signature field in the table, shall I just enter the path on that field?

3). when I scan the signature or if it comes from as an attachment in an email, shall I save it as bmp, gif or jpg (if I don't have options for png)


Thank you
0
Query to extract time and date from datetime column in Access

Time & Date(SEC)  column is      12/1/2018 12:01:20 AM

SELECT [Time Date TABLE].[Time & Date(SEC)], int( [Time & Date(SEC)]) as JustDate, [Time & Date(SEC)]-int([Time & Date(SEC)]) as justTime FROM [Time Date TABLE];

Query result is as follows:

Time & Date(SEC)                     JustDate               justTime
12/1/2018 12:01:10 AM           12/1/2018            8.10185185400769E-04

Date is getting converted correctly however time column gives some strange number.

Any help will be highly appreciated.

Thank you,
0
I have an excel file with two worksheets on it.  First work sheet is called "Assets" and Second Worksheet is called "Components"

I have an import button that works just fine when it comes to importing the assets worksheet to it's own table.  And it works just fine when importing the components to its own table.  The problem comes in is the components table is linked to the assets table.  So the components are going in, they just aren't being linked via the Asset ID.

I'm not sure how I could fix that issue.  We have 4K components and just as many assets that need to be imported.  I really don't want to enter the asset ID manually.

Below is the code I am using:

Private Sub Command164_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean
blnEXCEL = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\HWBL-IMPORT.xlsx", , True) ' opens in read-only mode

' Replace WorksheetName with the actual name of the 

Open in new window

0
I get the error attached in the screenshot when running a series of action queries (set in motion by code) to archive the customer transactions.
I tried running the action queries individually, and the query that produces the error (in the code below) does not contain the word or the field driver. There is a field "Driver" in both normal and archive sides, but they are of the same data format, and the form used to initiate this code, does not have a driver object - so I befuddled. Any direction would be greatly appreciated.

Rick

The query:

INSERT INTO ArcCusTrxItems
SELECT CusTrxItems.*
FROM CusTrx INNER JOIN CusTrxItems ON CusTrx.TrxID = CusTrxItems.CusTrxID
WHERE (((CusTrx.TrxDate)<=pForms("ArchiveCusTrx","EndDate")) AND ((CusTrx.TrxFinal)=True))
ORDER BY CusTrx.TrxID, CusTrx.TrxType, CusTrxItems.ProdCode
WITH OWNERACCESS OPTION;


DAO Error
0
Good morning Guys
I have a form in Access 365 called Orders from which I create several cloned forms with different Orders ID’s. The Orders form has several subforms, all with one-to-many relationships with full referential integrity. The problem is that all the subforms populate correctly except one. If I close the cloned form and open it as normal that is without cloning, its works fine. The code I use to open the cloned Orders form is as follows. Some pointers as to where I’m going wrong will gratefully received.
Regards
Cornholme

Function OpenAOrderForm()
Dim frm As Form
Dim recClone As Recordset
Dim LastOrd As String
Dim DrwNo As String
Dim frmName As Form

DrwNo = Nz(Form_frm_Orders_By_Customer_SubForm!DrawingNo, "Empty")
LastOrd = Form_frm_Orders_By_Customer_SubForm!OrderID

 Set frm = New Form_Orders '_View
 Set recClone = frm.RecordsetClone
 recClone.FindFirst "[OrderID]= " & LastOrd
 frm.Bookmark = recClone.Bookmark

frm.Visible = True
frm.Caption = "Drawing No: " & DrwNo & ",   QID: " & LastOrd

clnOrders.Add Item:=frm, Key:=CStr(frm.Hwnd)

Set frm = Nothing
Set rec = Nothing

Forms!orders!WorkorderSetupQuote.Form.Requery

End Function
0
I have a form which is used for two purposes, Quoting and Job Costing. There is a combobox to select which either of these options.

On the form is a Combobox (allow Inserts) which looks up a table of parts we are likely to use again a job.

If "Quoting" is selected we pick the item from a pre-populated table, then obtain a price from a supplier.

If, however, it is a "Job Costing" (i.e. the job has been carried out) we want the combobox to select from the table of parts used (which will have the cost we paid already available).

Thanks in anticipation
0
Question

Working on improving performance in an Access application. Some forms have multiple sub-forms and they load very slowly - often times bringing the entire server to a screeching halt. Before I start rewriting this thing I thought I'd ask for some advice.

One sub-form record-source is a linked SQL UNION QUERY - Does the Recordset Type (Dynaset, Snapshot, etc)  in Access matter --- as far as performance goes? None of the data is editable.

This query returns around 6300 records in the list and IS SLOW AS MOLASSES to load.

Having it be a UNION query is by design because the purpose of the form is to allow supervisors to view work loads across a sales floor for multiple types of Lead Records -- which are presently in two different tables (and in an ideal world would not be in separate tables -- but to accomplish that would require a complete rewrite and I don't have time to do it right now).

I could allow them to toggle between the two tables but they wouldn't get an overall picture easily --- hence the way it's designed now...

I'm going to add a couple indexes tonight to see if that helps -- just thought I'd reach out for some Expert Advise.

Anyway -- any suggestions would be appreciated. Thanks y'all!
0
Hello Experts,
Trying to perform some data validation on a sub form prior to saving. I read through Pat Hartman's recommendation here ( https://www.experts-exchange.com/questions/28965080/checking-subform-for-empty-or-null-values.html ) whereby Pat advocates the use of BeforeUpdate and DCount.

How can I conduct a similar operation on a SubForm & SubSubForm combo?  

As a first step, I tried creating an unbound field on the subform with

=DCount([InventoryRemovalID],"t-Inventory (Remove)","[BagLabelID]='BagLabelID'") but I'm getting #Name? error message.

Do I need to call out the control names in the DCount formula w/ proper SubForm and SubSubForm references ( Me!SubSubform.Form!ControlName).  Also, do I place the code in the BeforeUpdate in the SubForm?

Thanks,
Ken
0
My copy of MS Office got an upgrade last Friday and ever since then, some of my forms in MS Access, but not all, went from Border Style = Thin to Border Style = None.  In addition to this some, but not all of my date fields on forms went from Show Date Picker = For Dates to Show Date Picker = Never.

Is very frustrating.  Has anyone else run into this?
0
I am using the following code to transfer a spreadsheet to Excel and create an.xlsx file but it won't create the file.  I altered this code from using an .xls file that had previously worked with 2010 apps but have been upgraded to 2016 apps.  I changed all .xls code to read .xlsx and acSpreadsheetTypeExcel12 to acSpreadsheetTypeExcel12Xml.  Why won't it create my file?  It seems to bypass all the code until it tries to open the workbook and then I receive an error that the file doesn't exist.

Private Sub cmdExport_Click()

Dim pathf As String, fname As String, fnameo As String, paths As String
pathf = "\\corpfs03\SHARED\hcf&um\SHARED\Weight Watchers Database\" _
& "Weight Watchers - Reimbursements\Check Templates for AP\Current Year\Macro Templates\"
fname = "Template_Man_chk"
paths = "\\corpfs03\SHARED\hcf&um\SHARED\Weight Watchers Database\" _
& "Weight Watchers - Reimbursements\Check Templates for AP\Current Year\"

'file names with underlines
fnameo = fname & Format(Date, "mmddyyyy") & ".xlsx"
If Len(Dir(pathf & fname & ".xlsx")) > 0 Then
    FileCopy pathf & fname & ".xlsx", paths & fnameo
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "qryChecksSentToAP", paths & fnameo, True, "APChecks"
Else
    MsgBox "Original file is missing", vbOKOnly
End If

'WillR - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
    Set xlWB = 

Open in new window

0
I am getting the following error when I try to open my access database. Cannot open a database created with a previous version of your application.

Regards,

Dallag
0
I have continuous form with certain rows. as i want to sort ordering of rows i am using up and down buttons after update event
i have text box called sort which holds primary key field of back end sql table. when i click on up button its throwing error
run time error 3157update on a linked table woorkorderpayitem failed. i am using this vba code in then up button click event
Dim rst As Object
    Dim previousValue As Long
    Dim CurrentValue As Long
    Dim blnStop As Boolean
    If IsNull(Me.sort) Then Exit Sub

    Set rst = Me.RecordsetClone
    blnStop = False
    With rst
        .MoveFirst
        If Not .EOF And Not .BOF Then
            While Not .EOF And Not blnStop
                If .Fields("WorkOrderPayItemID") = Me.sort Then . (workorderpayitemID is primary key field and sort is name of text box holding that primary key)
                    CurrentValue = Me.sort
                    .Edit
                    .Fields("WorkOrderPayItemID") = previousValue
                    .Update . 'throwing error at this line
                    .MovePrevious
                    .Edit
                    .Fields("WorkOrderPayItemID") = CurrentValue
                    .Update
                    blnStop = True
                End If
                previousValue = .Fields("WorkOrderPayItemID")
                .MoveNext

            Wend

        End If
    End With
    With rst
End With
Set rst = Nothing
    Me.Requery
   End Sub

it …
0
When I got my first IBM - XT last century I had a paper log that I kept and it was invaluable.  I am using Access 2019 to build a computer log database.  We have 15 computers on our network and I want to use the log to track when updates to software, hardware and other event occur.  I also will use it to track computer issues so that when I encounter the same issue months or more in the future I can find that issue so I do not have to search for it all over again.  The main table is tblLogData.  I want to build a report that the user can first select the computer(s) from tblComputer and then the specific log event(s) from (tblEvent) to produce the report.  Can anyone point me in the right direction?
0
I need to convert  dates  to fiscal year and fiscal quarter. I would like two separate fields with fiscal year in the format "yyyy" and quarter formatted as a number (1,2,3 or 4) The fiscal year starts on 10/1.
0
I’m now almost finishing my task and everything appear to work very well except one last bit which I thought was the easiest to do, here is a puzzle:

After creating a combo box in ms access with the below query and the VBA to populate the selected columns from the mentioned combo box all is well no issue see below:
Combo Box query
SELECT tblPurchasesDetailslines.PurchaseOrderID, tblPurchasesDetailslines.PurchaseID, tblProducts.ProductID, tblProducts.ProductName, tblPurchasesDetailslines.Quantity, tblPurchasesDetailslines.CostValue, tblPurchasesHeader.StatusStore
FROM (tblPurchasesDetailslines INNER JOIN tblProducts ON tblPurchasesDetailslines.ProductID = tblProducts.ProductID) INNER JOIN tblPurchasesHeader ON tblPurchasesDetailslines.PurchaseID = tblPurchasesHeader.PurchaseID
WHERE (((tblPurchasesDetailslines.PurchaseID)=[Forms]![frmGrn]![CboOrder]) AND ((tblPurchasesHeader.StatusStore) Is Null Or (tblPurchasesHeader.StatusStore)<>"2"));

Open in new window


VBA (used to input now the selected combo box columns)
Private Sub CboProducts_AfterUpdate()
Me.PurchasesID = Forms!frmGrn!txtGrnsCosting
Me.ProductID = Me.CboProducts.Column(2)
Me.Qty = Me.CboProducts.Column(4)
Me.Cost = Me.CboProducts.Column(5)
Me.GrnStockAccName = "Stock"
Me.GrnStockAcc = "114-10-1000"
Me.SuspenseName = "GRN Suspense"
Me.SuspenseAcc = "121-12-1000"
Me.BSIDSTOCK = "2"
Me.BSIDSuspense = "3"
DoCmd.GoToRecord , , acNewRec
Me.Refresh
End Sub

Open in new window

All what I want is to simply capture/input all the rows & columns as per filter combo box instead of just one line. Whatever rows is in the filtered combo box
For example if the filtered combo box is showing 5 rows then all the rows must captured at once using the VBA above



Purchases-Invoice.png
0
I have a subform in a main form that opens in datasheet view mode.  When the form opens and if the current user is not a particular user then I want a particular field to NOT be visible to the user.  Here is my code but no matter what I try, the field is visible to the user.

Private Sub Form_Current()
On Error GoTo Err_Form_Current

    If DLookup("[CurrentUser]", "LOCALtblCurrentUser") <> "[i]username[/i]" Then
        Me.txtMonthlyManagementFee.Visible = False
    End If

Exit_Form_Current:
    Exit Sub

Err_Form_Current:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_Form_Current
    
End Sub

Open in new window

0
I have an older friend running Microsoft Office Professional 2003 on a Windows 10 Pro machine, a Dell Optiplex 3020.  She is trying to create a few basic reports using  Publisher and Access 2003 for her art group. She has an existing data base ported over from Publisher 98.  She's confused and I don't know enough about publisher and access to help her.  The art group will pay for the work.  Is it OK to solicit this type of work in this forum? I have Teamviewer on her computer.  
Thanks,
Alan
0
I have a corrupted Back End Table Autonumber I need to repair....Every time I try to run this in SQL in Access 2016, I keep getting a "Invalid Field Data Type"

ALTER TABLE Bdata ALTER COLUMN ID COUNTER(2600752,1)
0
Hello Experts.
I have a question that is based on a previous post.

I have a Form and a SubForm whereby the Form/Table record is automatically generated when a user enters data into the Subform.  

Private Sub Form_BeforeInsert(Cancel As Integer)

    If Me.Parent.NewRecord Then
      Me.Parent.CreationDate = Date
      Me.Parent.Dirty = False
    End If
 
End Sub  

This code is working fine.  

I have a field in the subform, [Qty], and I'd like to make this a required field <>0.  The simple approach would be to set this up as a required field at the table level, but when I attempt this, it seems that the code above is creating an error message (Error message is ~ A value is required for [Qty]) every time I click on a field in the subform.

How can solve this dilemma?  Can I add a condition to my existing code above to conduct the [Qty] validation only after the parent record has already been created....some sort of ELSE statement?

Thanks,
Ken
0
I'm trying to add a new record on a form with a command button.  The on-click event of the command button is...

Private Sub cmdAddRecord_Click()
On Error GoTo cmdAddRecord_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If

cmdAddRecord_Click_Exit:
    Exit Sub

cmdAddRecord_Click_Err:
    MsgBox Error$
    Resume cmdAddRecord_Click_Exit

End Sub

Open in new window


But when I click it I am not creating a new record.  The form opens as though I am creating a new record but I am not.  In fact, when I click on the pencil icon, I get "You must enter a value in the 'tblName.ID' field.  What is going on?
0
I am creating a database in Access in which I want to send an automatic email to notify certain staff members when a new record has been added.
I would to be able to determine which staff receive the email based on the results of a query which identifies who is currently receiving notifications;
i.e. I wish to change recipients using a query rather than changing any underlying VBA.  I also wish to attach a report to the email.  

I am a VBA novice at best, I am attempting to do all of this using a DoCmd.SendObject; it it possible to do this using the DoCmd or is there a better way?

Thank you
0
I have been using an internal mail relay server to send emails from Access using this code:
    Set objMessage = CreateObject("CDO.Message")
    With objMessage
    
        .Subject = Subject
        .FROM = Environ("Username") & "@domain.com"
        .To = Me.txtTo
        .TextBody = Me.txtMsg
    
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xxx.xxx.xxx.xxx" (Relay server IP)
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "25"

        .Configuration.Fields.Update
        
        .Send
    
    End With

Open in new window


Our external IP has been blocked from sending email due to a PC with malware sending out 100's of emails so I now need to use the Office 365 SMTP so I tried this:

    Set objMessage = CreateObject("CDO.Message")
    With objMessage
    
        .Subject = Subject
        .FROM = Environ("Username") & "@domain.com"
        .To = Me.txtTo
        .TextBody = Me.txtMsg
    
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com" 
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "587"

        .Configuration.Fields.Update
        
        .Send
    
    End With

Open in new window


However this does not work, I get error "The server rejected the sender address. The response was: 530 5.7.57; Client was not authenticated to send anonymous mail during MAIL FROM"

Does ayone know how to make this work?
0

Microsoft Access

221K

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.