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

Hi Experts
I want to convert the receipt number (no_esal) instead of writing it with my hand being automatically .. When writing the date in the month of 7 as example starts with the number 1 to the end of the month .. When writing the date of a new month for example month 8 starts counting again with a number 1
as shown in pic.
no_esal.PNG
0
I have a subform and I have created a recordset clone. I would like to get the year field data from that recordset clone but grouped. The data in this case is a datefield and I want the year part only but grouped.

so I get
2016
2017
2018

and so on

I have this so far:

    Dim rstTclientData As DAO.Recordset
    Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone
   
    With rstTclientData
   
   
    End With
0
Hello Experts,
I have a sub sub form with a data entry field, [Measurement].  This sub sub form also has a field named [Status].  

If errant data is entered into [Measurement], I write the value "Error" into the [Status] field w/ an After Update event on the [Measurement] field.

Prior to saving a record with [Status]="Error", I want to present the user with a courtesy Popup Message Box that reads "Entered value is out of specification."  If the user hits okay, record saves, if they hit cancel, focus is returned to [Measurement].

How do I accomplish this and where does the VBA reside?

Thanks!
0
I have a simple select query as shown in the attached file.  When I run it the order of the columns is not the order I selected them.  Can this be fixed?AccessColumns.docx

This is in Access 2003 and sorry, I can't use a later version.

thanks.
0
Hello Team

I have been attempting to update into a table a default date of #01/01/1900#.  Code shown below.

If Me.txtStartDate.Value = "No start date found" Then
    
If Me.txtStartDate.Value = "No start date found" Then
    
     strSQL = "UPDATE TblRenDatabaseNM Set StartDate = #01/01/1900#, EndDate = #01/01/1900# " _
           & "WHERE ID = " & Me.ID
     CurrentDb.Execute strSQL, dbFailOnError


End If

Open in new window


When I run this part of the code I move to an error handler stating the following:

Error Number 3075.  Description Syntax Error in Date in query expression #01/01/1900#

The field to hold the dates are set to Date/Time with a Short Date formatting.

Any suggestions would be greatly appreciated.

Thanks

Dale
0
Hello Experts,
I have a button on a form that launches a query.  The query has criteria that prompts a user to enter some info [Enter Packing Slip Number].  If the user clicks the button to launch the query, but then decides to abort the process (click cancel at the Enter Parameter pop up), Access generates an error message.  How can I suppress this error message?

The button was created w/ Access' design wizard, so, it is listed as a macro in the "On Click Event".

Thanks,
Ken
0
I copied this code (UploadXML) from VB Forums and have used it in a MS Access 2003 application for over two + years now. BUT suddenly it has stopped working possibly due to a MS update? It now longer uploads the XML file.
Function UploadXML(strformurl As String, strFileName As String, inputfile As String, Optional strUserName As String, Optional strPassword As String) As String
    Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0
    Const HTTPREQUEST_SETCREDENTIALS_FOR_PROXY = 1
    
    Dim WinHttpReq As WinHttp.WinHttpRequest
    Dim strBody As String
    Dim strFile As String
    Dim aPostBody() As Byte
    
    Dim bound As String
    Dim boundSeparator As String
    Dim boundFooter As String
    
    bound = "AaB03x"
    boundSeparator = "--" & bound & vbCrLf
    boundFooter = "--" & bound & "--" & vbCrLf
    
    Set WinHttpReq = New WinHttpRequest

    WinHttpReq.Open "POST", strformurl, False
    
    If strUserName <> "" And strPassword <> "" Then
        WinHttpReq.SetCredentials strUserName, strPassword, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
    End If
    
    WinHttpReq.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & bound
    strBody = boundSeparator

    strBody = strBody & "Content-Disposition: form-data; name=""" & "login" & """" & vbCrLf & vbCrLf & strUserName
    strBody = strBody & vbCrLf & boundSeparator

    strBody = strBody & "Content-Disposition: form-data; name=""" & "pass" & """" & vbCrLf & vbCrLf & strPassword
    strBody

Open in new window

0
I have a table ("LocalImageTb") with two fields:  LogoDescription (short text) and Logo (attachment).  I need to display (and change) the .bmp image in the Logo field on a form NOT BOUND to LocalImageTb.  

This code is not working:
    Me.LogoDisplay.ControlSource = DLookup("Logo", "LocalImageTb", "LogoDescription = 'LogoNG'")

Not workable solutions for my application:
   Binding LocalImageTb to the form.  
   Calling an external image file with a fully qualified path.

The attached db file has examples of the unworkable solutions (displayed on AutoForm and BoundForm).  UnboundForm has my best guess at code that should work, but doesn't.  

Can anyone help?
DisplayLogo.accdb
0
I have main form, which has subform1 and this subform1 has other subform which is called subform2
main form is having an  unbound textbox with control source =Format(DLookUp("[WorkOrderTotal]","vw_WorkOrderTotalValue","[vw_WorkOrderTotalValue]![WorkOrderID] = WO"),"Currency") and i am getting expected result which is good for existing forms.
i am getting  values of this textbox from backend sql view(vw_WorkOrderTotalValue) which  was linked to access
WorkOrderTotal value is calculated field which is dependent on subform2 values
my problem is i want this text box to automatically updated whenever subform2 values changes or for new forms it needs to updated automatically when user enters values in subform2.
i tried using after update event for subform2 control but no luck, maybe i didn't referred mainform control correctly or some other reason
can someone kindly help me how can i update that main form value whenever subform2 value changes? i need vba code how to refer that main form control in subform2 and in which event i need to add that code
thanks
0
Hi,
I have a MS access form with multiple field. I like to know if there is a way the form saves data if there is a change in any field without having to use the after update
event on each field.
Is there a way to use the DoCmd.RunCommand acCmdSave function for all the field ?

The form has lot of fields and I have lot of forms, so if I have to put DoCmd.RunCommand acCmdSave on each field on after update may be a pain,  so wondering if there is a simpler way to save data any time a change is made on any field ??
Thanks
0
Hi
I am using Microsoft Access VBA.
I would like to know how to get the path where Firefox automatically saves downloaded files.

Thank you.
0
I have a table ("LocalImageTb") with two fields:  LogoDescription (short text) and Logo (attachment).  I've added attachments to two records, and populated the LogoDescription field with LogoA and LogoB.  
I have a form with an attachment control (named "LogoDisplay") and a button.   I want to put code behind the button that will display the image from one of the records.

This code is not working:
Me.LogoDisplay.ControlSource = DLookup("Logo", "LocalImageTb", "LogoDescription = 'LogoB'")

Open in new window

No error message, but no image appears, either.

What am I doing wrong?
0
I have a form opening in read-only mode but on the form is a combo-box that I want the user to use to find a record and then in the after update event of the combobox, have the form go to the selected record.  But since the form is in read-only mode, the combobox doesn't work.  Is there a way to allow the combobox to work?
0
Hi Experts,
Is anyway I can get the first 50 records and the last 50 records in a query?  The PK in the table is not an autonumber, it a text field but with number.
below is the query:  (studentNo is PK). I wan to pull the first 50 records for one report and the last 50 records for the second report.

SELECT tblMain.StudentNo, tblMain.Description, tblMain.Site, tblMain.Location, tblMain.Class, tblMain.Category
FROM tblMain;


Thanks,
0
Access:  I need help with the following query. I need to find all the TechID  that have a PM with and without a RTC.  I can get the ones that have a RTC or the ones the don't have a RTC but I need the ones that have a PM that is YES for RTC and that have a PM that is NO for RTC.

SELECT tbl_TechPMLink.PM_ID_FK, tbl_PMs.New_Crafts, tbl_PMs.P_Interval, tbl_PMs.Outage, tbl_PMs.Clearance, tbl_PMs.RTC, tbl_PMs.Duration, tbl_TechPMLink.EQ_ID_FK, tbl_TechIDS.TechID, tbl_TechIDS.AssetName
FROM tbl_PMs INNER JOIN (tbl_TechIDS INNER JOIN tbl_TechPMLink ON tbl_TechIDS.EQ_ID = tbl_TechPMLink.EQ_ID_FK) ON tbl_PMs.PM_ID = tbl_TechPMLink.PM_ID_FK
WHERE (((tbl_PMs.New_Crafts)="I" Or (tbl_PMs.New_Crafts)="M" Or (tbl_PMs.New_Crafts)="E" Or (tbl_PMs.New_Crafts)="O"));

Open in new window

Example:
PM_ID    New_Crafts   P_Interval   RTC          EQ_ID_FK    TechID                          AssetName
439                   M               17520        TRUE            1034      1-FB-MTR-5002A          PSSO0049940      
461                   M                 4000        FALSE            1034      1-FB-MTR-5002A          PSSO0049940
439                   M               17520        TRUE            1036      1-FB-MTR-5002B          PSSO0050140
461                   M                 4000        FALSE            1036      1-FB-MTR-5002B          PSSO0050140       
439                   M               17520        TRUE            1038      1-FB-MTR-5002C          PSSO0050340      
461                   M                 4000        FALSE            1038      1-FB-MTR-5002C          PSSO0050340
0
I want to select all the items in a combobox in Ms Access as you can see the combo query below is already filtered so that only the specified record can be populated.


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


Instead of select line by line how can I select all the in combobox since the combobox details is already filtered, it very time consuming when capturing data line by line , for example imagine a purchase order may have 2000 lines now when creating a goods received note why not selecting all the lines in the GRN combo instead of line by line until all the 2000 lines finishes surely there must a way. (Selecting all mean all the data will populated as captured in the datasheet , example all the 2000 lines will show captured rather than selecting one by one)

GRN-Population.png
Regards

Chris
0
Hello,

I have a form and and a subform.  From the subform, I open a window using a button.  On the close of that window I refresh the subform. I would like my cursor to return to the same place.  I have some code currently whereby I capture the record and then move my cursor there... however, the record moves up to the top of the form and it is just a bad experience for the user.  I have attached an image to illustrate my issue.

Thanks in advance.
Joy
Picture1.png
0
Hello, can you please assist me as to why my Browse button is bombing out on me: it doesn't like this line for some reason....:
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"

Option Compare Database
Option Explicit

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant

    Set diag = Application.FileDialog(msoFileDialogFolderPicker)
    diag.AllowMultiSelect = False
    diag.Title = "Please select an Excel Spreadsheet"
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"
   
    If diag.Show Then
       For Each item In diag.SelectedItems
           Me.txtFileName = item
       Next
    End If
End Sub
0
I am designing a tabbed form that will not stack.  I have set Multi Row = Yes but the tabs keep going horizontally.  What am I doing wrong?
0
HI Experts

I want to simplify the posting of the Good received note in ms access , the current position is that we have to select the purchase order from the parent grn form ,then in the grn detailline form you have to select the reflected order line one by one.

What is required here now is that when an order is select from a combo called CboOrder based from a parent form frmGRN, then what is in the subform sfrmGRNdetailline in a combo called ProductID must populate in the data sheet automatically

Example the parent combo hold the order number , so we want to be just selecting the order number and everything is populated in the data sheet


GRN-Population.png


Any code to used will be highly appreciated otherwise selecting lines 1 by 1 is not the way to go


Regards

Chris
0
Hi Experts!

I want to be hiding the copy text if the tblCustomerInvoice in field StatusOne  has no 1 and show it when a 1 is updated there below is my code:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
If "1" = DLookup("StatusOne", "tblCustomerInvoice", "[InvoiceID] = " & Me.InvoiceID) Then
Me.lblbetty.Visible = False
ElseIf (DLookup("StatusOne", "tblCustomerInvoice", "[InvoiceID] = " & Me.InvoiceID) = Null) Then
Me.lblbetty.Visible = True
ElseIf (DLookup("StatusOne", "tblCustomerInvoice", "[InvoiceID] = " & Me.InvoiceID) = Null) Then
Me.LblSams.Visible = False
ElseIf "1" = DLookup("StatusOne", "tblCustomerInvoice", "[InvoiceID] = " & Me.InvoiceID) Then
Me.LblSams.Visible = True
Exit Sub
End If
End Sub

Open in new window


Regards

Chris
0
How do I create an Import Table in Access for a monthly raw (May, June, etc) data tab in Excel that I can use monthly to capture the new raw_data?
0
Experts,

I have an Access database where we allocate cost to some combination of say 1,000 cost centers. Let's say that the cost centers that receive allocations can be organized in a four level hierarchy
Cost Center   Company         Division    Business Unit
1000                 Co Name             Div 1          BU1    
1001                 Co Name             Div 1          BU1
1002                 Co Name             Div 1
1004                 Co Name             Div 2          BU2
1005                 Co Name             Div 2          BU2
1006                 Co Name             Div 3          
1007                 Co Name             Div 3        

I'm looking for a method to select the required cost centers by any combination of
1) individual cost centers,
2) all cost centers in the company,
3) all cost centers in a Division,
4) all cost centers in a business unit

So for example, we could select cost center 1000, BU2 and Div 3 and the database would be able to associate the following cost centers to the allocating cost code
Cost Center   Company         Division    Business Unit
1000                 Co Name             Div 1          BU1    
1004                 Co Name             Div 2          BU2
1005                 Co Name             Div 2          BU2
1006                 Co Name             Div 3          
1007                 Co Name             Div 3        

I'm looking for direction on how to do this. We already have a …
0
When I try running an Access report, I immediately get the following Access error message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Strangely, I am able to run the report's underlying query without error. The report has minimal VBA code, and the error occurs before the first line of code--when I create a breakpoint at the first line of code that executes when the form loads. Why might the report have this error but the query runs fine?

The backend is a SQL Server database.
0
In Access, I am using a Form, "F_search Form"  and Subform, "F_Out of Sync List". I am using combo boxes on my main form to filter and search on the subform.  For example if you enter the date 7/1/2019 on the search box you will see 3 records appear.  Once you select the record you want, click on the open selected record button on the bottom left and a new form "Input Form" will open up.    On the Input Form on the bottom there are 3 buttons.  what should happen is when you click on close record button the current date will appear which is connected to one of the Table "T_out of sync table after".   After that you will click on the close form button and that where i want the form to close and to Refresh or requery the subform  that contains all the records. At the end i should see 2 records instead of 3.  

Private Sub Form_Close()
Forms![F_Search_Form]!F_Out_of_Sync_List!Record_ID.Requery
End Sub

i tried to put this code on the input form but i am getting this error
Run-Time Error '2450'
Microsoft Access cannot find the referenced form "f_search_form'

Help!!!
Database-Test.accdb
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.