Microsoft Access

221K

Solutions

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

Hi,

I am using MS-ACCESS as a front end.  MySql as Back end.

I have a DELETE query, to delete the content of a Table (No conditionals on the query at all, so delete all the records in the table).

But for some reason, the query is deleting all the records, except one record.   I executed the DEL query again an still not able to delete that specific row or record.

Also, the table shows #delete# in all the fields if I open the table, but if I refresh the table, the same record appears again.

Please advise.
Thank you.
0
Hi,

I have put some restrictions to users to delete records from a Sub-form.   However, I notice that one of them is using Control+A and then delete.

How to avoid the use of Control+A (Select all records).

Thank you,
0
I've been working on this function all day and finally getting it close. ( i think )

I have a form with a subform that is hosting a query. I'm trying to filter the query
based on a selection from my Combo box.

I'm getting a Run-Time Error 2465
Microsoft Access Can't find '|1referred to in your expression



Option Explicit
Dim sPartsListFilter As String
-------------------------------------------------------------------------------------------
Sub ApplyFilter()
sPartsListFilter = ""

If Me.cboAccronym.ListIndex <> -1 Then
    If sPartsListFilter = "" Then
    sPartsListFilter = "[Accronym]='" & Me.cboAccronym & "'"
    Else
    sPartsListFilter = sPartsListFilter & " And [Accronym]='" & Me.cboAccronym & "'"
    End If
End If
--------------------------------------------------------------------------------------------
Me.[qMasterPartsList].Form.Filter = sPartsListFilter
Me.[qMasterPartsList].Form.FilterOn = True
End Sub
--------------------------------------------------------------------------------------------
Private Sub btnClearFilter_Click()
Me.cboAccronym = ""
ApplyFilter
End Sub
--------------------------------------------------------------------------------------------
Private Sub cboAccronym_AfterUpdate()
ApplyFilter
End Sub
--------------------------------------------------------------------------------------------
0
Compact Repair Removing Tables from the ACCDB

Short version: I recently upgraded my office machines to Win 10 Pro.  Now, when I pull an accdb from my client’s machine to my office machine, then run a compact/repair on the transferred data, on my machine, tables are lost.

This is new behavior since I upgraded my office machines to Win 10 Pro.  I have been working with this client for 10 years and many times have pulled accdb’s from their machine to my machines and compacted them with no issues.  I tried this on another machines in the office that was recently upgraded and the compact/repair also removed tables.

Here’s the current configuration.

Client Machine            Windows 2012 R2      Access 2013
Office machine            Windows 10            Access 2013

Compacted ACCDB size      1.03GB


Much longer story with much more, probably too much, detail
I am adding new functionality to a client’s Access 2013 application.  I wanted to test with their current data so I pulled it to my office machine from their server.  The first thing I did on my office computer was run a compact/repair.  At the end of the compact repair there were tables missing from the accdb.  I thought maybe the data was corrupted so I created a blank database, pulled in all of the tables from their original, non-compacted DB and then ran the compact repair on the new DB.  Same result, tables were removed.

I connected back into the client’s server backed up the accdb, then ran a compact/repair on their server.  No issue …
0
How do I get rid of this arrow? What is it called and what is its purpose?\

See the attached picture, The arrow is circled.

How to remove arrow icon from form.
0
This is driving me crazy. I have a form with a subform. On the subform, each record has a percentage field. On the main form I have a text box showing the total percentage. On my Adds and Updates it recalculates exactly as expected. BUT I can't seem to get it to work after deleting a record on the subform.

What I want is, after a deletion (after the record is actually deleted!) for the text box on the main form to recalculate. I'm not using Delete Confirm, so please keep that in mind. I've tried using a flag that triggers on deletion and then check that flag during the On Current, but that didn't work either.

There must be a way to do this. I think I've done it before, but can't remember.

At what point AFTER a deletion can I run a procedure that will calculate a field on a table.

Any help would be greatly appreciated.

Thanks,
Eddie
0
MS Access 2016 - Need to have 2 forms shown on one Screen
0
I have a form (1) that contains a sub-form (2), a sub-sub-form (3), and a sub-sub-sub-form (4).

I need to reference the value in a field (is this also called a control??) on (3) as a criteria in the query behind (4).  I tried the Me.Parent!ControlName nomenclature (Me.Parent![PartNumber]), but I'm not getting any effect,  

Is there a way to use some sort of relative reference for the query criteria behind (4)?

Thanks,
Ken
0
I have a basic Form question -

For lack of not knowing any better, I have created multiple copies of the same form to handle different scenarios.  For example, I have a form name f-order and I have second similar form named f-order_edit.  f-order_edit is opened when a user clicks on a button (located on a third form), which uses a criteria in the f-order_edit underlying query to one return one specific record.

There is probably a better way to handle these two scenarios with a single form + some sort of "where" expression that is associated with the button...right?   Where would this code be placed...on one of the button's events?

Thanks,
Ken
0
access 365
excel 365

Trying to make a column a certain width.
Keep getting  ERROR   object variable not correct ?


tryying to use this in the code below
   ' Column width adjustments
     With .Cells(1, 1)
        .EntireColumn.ColumnWidth = 15.29
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
       
      

Open in new window



function:
Function Export( _
                     query$, path$, _
                     fileName$, wksName$, _
                     colsCurrency$, colsDate$ _
                     ) As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Error GoTo errHandler
   Dim xlApp As Object, wkbk As Object, wks As Object
   Dim file$
   Dim formatCur$, formatDate$, intColor&
   Dim arrayCols() As String, col$, n%, i%, w!
   Dim cell As Range
   Dim msg$
   ' Worksheet formats
   formatCur$ = "$#,##0_);($#,##0)"
   formatDate$ = "dd-mm-yyyy"
   intColor& = RGB(192, 192, 192)
   ' Create workbook
   file$ = path$ & fileName$
   DoCmd.TransferSpreadsheet _
      TransferType:=acExport, _
      SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
      TableName:=query$, _
      fileName:=file$, _
      HasFieldNames:=True
   ' Open workbook
   Set xlApp = CreateObject("Excel.Application")
   With xlApp
      .Visible 

Open in new window

0
I need help to place the order of a string "864"into Big-endian before conversion to binary:

Private Sub Cmdendians_Click()
Dim strstrong As String, datastr As Byte
datastr = Len("864")
DecimalValue = datastr
BinaryValue = DecToBin(DecimalValue, 8)
Debug.Print DecToBin(DecimalValue, 8)
End Sub

Open in new window




Screen Shoot
string-to-binary.png
0
I have an access DB made up of around 30 tables, all with the same fields, and I need to search a specific field on all tables (ideally in one query) for a certain value. In this case I specify the search term, but it would also be useful to search the field for another tables worth of search terms, so some guidance how to do this for both scenarios would be useful.
0
Detect Changes.

Hi,

I have a form in MS-ACCESS.   When load the data, a check control = Yes (-1).

I want that the check control changes to No (0) as soon as any change in the form is detected.
How can I detect any change in the form and assign the "No (0)" value to the check control?
0
I need a very simple audit trail to be developed.  All I need to capture from a form's edit is the bound table name and a field value named txtPrice (Price) and the date it was changed.  And then only when the Price changes.  I'll need the original value and the new value.  Does anyone have a simple solution?
0
Access:  I have a SELECT query and I made it a CROSSTAB.  How do I get it to go back to a simple SELECT query?
0
Hi

I am trying to password protect excel workbook in code below. but the script (pointed) in below vba code does nothing. After running this code below, I can open workbook Report19.xlsx as usual.

Public Sub A1()
Dim ObjExcel
    
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\Users\abc\Desktop\Report19.xlsx"
     
    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(1)
    ObjSheet.Activate
    
        ObjSheet.Range("A1", "A2").HorizontalAlignment = xlCenter
        ObjSheet.Range("A1", "A2").VerticalAlignment = xlCenter
        ObjExcel.ActiveWindow.DisplayGridlines = False
      
    With ObjSheet
        .Rows("1:1").Font.Size = 12.75
        .Range("1:1").Font.Bold = True
        .Range("A1").Font.Color = RGB(255, 255, 255)
        .Range("A1").Interior.Color = RGB(0, 81, 142)
        .Range("A1").Select
        End With
        
        ObjSheet.UsedRange.BorderAround LineStyle:=xlDouble, Weight:=xlThick, ThemeColor:=4
        ObjExcel.ActiveWorkbook.Protect Password:="abc123", Structure:=True, Windows:=True '<-------------------------------
        
        
    ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
    
    Set ObjExcel = Nothing
    Set ObjSheet = Nothing
    Set FilDatCretd = Nothing
End Sub

Open in new window


Thank you
A
0
So, this is a general question about best practices for building Access solutions. I'm building a new system. In the past, when I've built "main" tables that have lookup fields, I always used the Lookup Wizard on the field type and associated the field with the lookup table right in the table design. But I was wondering what you experts think about that. Should you leave the field as Numeric and design the drop down only on the forms? Does it matter? Is there a speed or index issue?

Let me know your thoughts.

Thanks,
Eddie
0
Access:  Need the most recent date value from 3 fields.

PONUM     RECNUM          DATE
123                 0001          01/02/20
123                 0002          02/02/20
123                 0003          03/03/20
123                 0004          04/04/20


Need to return  >     123                 0004          04/04/20
0
access 365


I keep getting  type mismatch  on this statement ?

racfid = 'bbbbb'
f.fields(1)  =  "2/5/2020 10:52:53 AM"   ' date format
Date is ahowing as  "02/05/2020"

  ' set the access temp local table just in case i need it.
Set r = CurrentDb.OpenRecordset("Select [racfid], [Logged_In], [Logged_Out] from qry_CurrentUser where [racfid] = '" & racfid & "'", dbOpenDynaset, dbSeeChanges)


If Len(Nz(r.Fields(1), "")) > 0 Then
         If (r.Fields(0) = racfid) And (DateValue(r.Fields(1)) = Date) Then
           MsgBox "You are Logging out of the Tracker, vbCritical", "Log Out Issue"    <--------- error here ?
           r.Edit
           r![Logged_Out] = Now()
           r.Update
           r.Close
           Set r = Nothing
0
Hello Experts,
I have a main form that contains two sub forms.  The Link Master Field for both Sub Forms is [Lot_Number].

Sub form #1 is based on q-LotNumberContainerWeights, uses Datasheet View, and is the data entry form.  In this case, a user is entering the [Weight] of various containers that are unique to a particular [Lot_Number].

Sub Form#2 is based on t-LotNumberContainerWeightTotal and displays the [Total_Weight] of all containers from [Lot_Number].

I would like the [Total_Weight] shown in Sub Form#2 to be updated after the user enters/modifies data  into Sub Form#1.  

How do I accomplish this?

Thanks
0
i am looking for a procedure to export references from one database to another in ms access vba
0
Hello. I have a very simple db (ms access for office 365). I created a report in order to see how I want to arrange the data. The report is based off a query. I have three test records. One of my fields on the report is called "AttributeValue". I then created an unbound text box and called it Pdesc. What I am trying to do is build a simple if statement. I placed the following statment on in the On Load event procedure of the report:

If [AttributeValue] = "36" Then
[PDesc] = "Test"
End If

When I preview the report my unbound text box does not populate if the condition has been  is met. What am I doing wrong?
0
I have two Access databases I created about five years ago. One has the tables called Master.  The other is linked to the Master.  Recently, when I open the Master all the objects in the Navigation Pane are not displaying.  My initial thought is that they are hidden, so I went to Navigation Options to ensure "Show Hidden Objects" were selected an that did not work.  I know the objects are still there because I can create a new query and I see all the tables.  The linked databases are also functioning just fine.  How do I resolve this issue.  I will attach the file if you need to see it to answer this question.  Thanks Julie
0
Dear All;
I need help to convert some Hex text to binary code using Ms Access VBA, see below:
Header1 :  0X1A
Header2 : OX5D
CmdID: (0x01,0x02 & 0x03)
Length : ( Content to big-endian)
I have tried to convert the above string using the online convertor, see the screen shoot below, for example
(1)       0x01 = 00110000 01111000 00110000 00110001
The objective here is send a command string to the serial port in the recommended format below:
<Header1><Header2><CmdID><Length><Content><CRC>
Both the content & CRC is already done , the problem is the four ( Header1 to Length)

Simple VBA code:

Dim Header1 as string, Header2 as string , CmdID as string, length as string, Content as string , CRC as string
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError  As String
Dim strData   As String

Header1 = XXXX (converted to binary code)
Header2 = XXXX (converted to binary code)
CmdID = XXXX (converted to binary code)
Length = XXXX (converted to binary code)
Content =????? (converted to binary code all in Json)
CRC = XXXX (converted to binary code)
strData = <Header1><Header2><CmdID><Length><Content><CRC>
  ' Initialize Communications
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=9600 parity=N data=8 stop=1")
    
    If lngStatus <> 0 Then
	' Handle error.
        lngStatus = CommGetError(strError)
	MsgBox "COM Error: " & strError
    End If
    

    ' Set modem control lines.
    lngStatus =

Open in new window

0
Hello, I have a form and I want to filter a combo box on this form.  I am having a difficult time doing this.  I click the small drop down icon on the header and it gives me an option to filter by what text I type but it doesnt not filter. I have created a button on the form and chose Apply Filter but that doesnt work either.  A macro is created in the button but there is no code in the macro.

How can I create a filter on a form to filter a combo box on the form?  I want to be able to choose what text to filter on.  

thank you.
0

Microsoft Access

221K

Solutions

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.