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

Dear experts,

What options are there in Access to automatically create a backup of the complete database at opening of the database or daily at a specific time? Preferably when the Access db is opened that a window asks the user if the user wants to create a backup of the database?
0
Why do I sometimes get empty records in a table when I import from an Excel file?  Here is an example of my VBA code for the import:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTEMPBaselineSurveyDataImport", Me.FileList, True
0
Hi Experts,
I have a question about Combo box in access 2016.  The combo box was working fine in my program before I convert to Office 365 Access 2016.  Now the combo box in Access 2016 has a problem that I couldn't figure it out why it happen and don't know how to fix.  The problem is:
I have a combo box that has a list of items from a query, the length of the items some are short and some are longer.   when users select from the combo box, the shorter length items show no problems, the users can see the item in the combo box right after they selected.  But there is a problem with the longer text item, it cuts off the first half part of the item right after the users select and only shows the other half part of the item in the combo box until users click on the any field in the form then the whole item shows in the comboBox.  I don't know why it happen in access 2016.  Any help how to fix would be very appreciated
0
How can I execute an Access 2002 query listing every table in the db and the record count in each table?
0
I have legacy code that exports Access queries to excel and formats the output.  The database that was created on a 32-bit Windows operating system and is giving me major headaches on my new 64-bit system.  I've done research and most experts say that it's best to uninstall the 64-bit system, but that is not an option for me, as IT dictates what's installed on our computers.  So what I need to know how to rewrite the code to work with the new 64-bit system.  Thank you!
Code_Snippet.png
0
Dear Experts!

Here is my problem in Ms Access VBA Code , the code which is supposed to write to the serial port is not fed with data as a result it cannot write anything to the port, below is the actual peice of code that require fixing:

VBA CODE 1

' Write data to serial port.

   
 strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3) & Chr$(13)
    lngStatus = CommWrite(intPortID, strData)
    lngSize = Len(strData)
    If lngStatus <> lngSize Then
    Beep
    MsgBox "No data found"
    ' Handle error.
    End If

Open in new window



Audit proof

If I send the same data to a text file , I'm able to see the actual Json data in text form by using the CODE below:


VBA CODE 2

Dim json As String
Dim theFileName  As Variant
json = JsonConverter.ConvertToJson(transaction, Whitespace:=3) & Chr$(13)
theFileName = "C:\Users\admini\Desktop\Leaders\VBA Json.txt"
Dim handle As Integer
handle = FreeFile
Open theFileName For Output As #handle
Print #handle, json 'jsonSerialization
Close #handle

Open in new window



See also screen shoot which confirms that there is no data at all written the port:

No-data-found.png
0
Folks,
In my Access 2016 form I have various fields that when you click on their label it will sort the table Descending. For example, if I clicked on the label Address for the Address control then then table is sorted Descending. What I haven't figured out is if I wanted to then click on the same label it would sort Ascending.

Private Sub Address_Label_Click()
        Me.OrderBy = "Address"
        If Me.OrderBy = "Address" Then
           Me.OrderBy = "Address DESC"
        Else
           Me.OrderBy = "Address"
        End If
End Sub

Open in new window

0
Dear All

See how you can help me to resolve the Ms Access VBA error . Runtime error 424  object required,  see the screen shoot attached:

VBA Code

Dim json 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
    Dim strDataToSend As String
    Dim lngSize As Long
    intPortID = Forms!frmLogin!txtFinComPort.Value
    ' Initialize Communications
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=115200 parity=N data=8 stop=1")
    
    If lngStatus <> 0 Then
    ' Handle error.
        lngStatus = CommGetError(strError)
    MsgBox "COM Error: " & strError
    ElseIf lngStatus = 0 Then
    Beep
    MsgBox "The port is Open"
    End If
    

    ' Set modem control lines.
    lngStatus = CommSetLine(intPortID, LINE_RTS, True)
    lngStatus = CommSetLine(intPortID, LINE_DTR, True)

    ' Write data to serial port.
    strData = JsonConverter.ConvertToJson(transaction, Whitespace:=3) & Chr$(13)
    lngSize = Len(strData)
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> lngSize Then
    Beep
    MsgBox "No data found"
    ' Handle error.

    End If
   
' Read maximum of 64 bytes from serial port.

Dim JSONS As Object

    lngStatus = CommRead(intPortID, strData, 14400)

Set rs = db.OpenRecordset("tblEfdReceiptsPOS")
    If lngStatus > 0 Then
    Set JSONS = JsonConverter.ParseJson(strData)
    Z = 2
    ElseIf lngStatus < 

Open in new window

0
When using this expression:

Me.RecordSource = "Select * from Lock Order BY Lock Desc"

Is there any way to include a second column or field order at the same time. I.e.:

Me.RecordSource = "Select * from Lock Order BY Lock Desc" and then Me.RecordSource = "Select * from Lock Order BY Size Desc"
0
Hi

What Access VBA code would I use to calculate the date of the next 31st of March, whether that be in this year or the next?

Thanks
0
I am using ms access as a front end for a mysql database.   I need to back up the database from where the ms access engine is located...i mean from a pc that is not the server.   How?
0
I have a report that I am trying to export to an Excel file but no matter what I try, the information doesn't come over correctly.  Anyone have any thoughts?
0
Hello all:

I have two forms (MS Access 2010).  The main form allows an end user to select certain materials.  Once they select an item and then press 'Process', it the material has serial numbers, it displays the second form (acDialog), so the end user can select the serial numbers they're processing.  What I would like to happen is if an end user clicks the x at the top of the second form (the serial number selector), I want all execution to stop.  I thought if I create an Err object it would do that, but it throws the error and then continues execution on the main form.  Is there a way to completely stop execution of the code?

Many thanks!

Juan
0
An office here needs to send out around 1200 mailing packs.

But they are not going to be done all at once. They will be sent out around 40 a day.

What is the best way to track which ones have been sent out and which ones have not?

Presently, they are using Avery-type labels.

Is there a barcode or QR code solution where packets could be scanned on exiting the office?
0
In Access or Excel VBA - how to debug.print the current sub or function name I'm utilizing?
As an explanation please note the following code:

Let's say I do in the Immediate window, I type Debug.Print UserName
as regards also giving me my windows sign-in name I also want it to print the name of the function also

Is there anyway to do this via code?
Cause then I wouldn't have to manually type the "modCommon @ UserName_feo" for my error log for each and every sub and function I have?

'grabs windows user sign in
Public Function UserName() As String
On Error GoTo Err_Proc

  Dim sValue As String
  sValue = ""
  Dim WshNetwork As Object
  Set WshNetwork = CreateObject("WScript.Network")
  sValue = WshNetwork.UserName
  Set WshNetwork = Nothing
Exit_Proc:

 '*** this is the line I'd like to get working...
  'Debug.Print (<---- the name of the function?)

  UserName_feo = sValue
  Exit Function
    
Err_Proc:
  Call LogError_feo(Err.Number, Err.Description, "modCommon @ UserName")
  Resume Exit_Proc
    
End Function

Open in new window

0
Hi. I want to use the following code to check whether a date entered is valid.How do I cancel the update of the field if the date entered is not valid?

Private Sub DateofBirth_AfterUpdate()
    'Test if applicant is under required age.....
    Dim CurrentAge As Integer
   
    If oCountry = "Samoa" Then
        oModalAge = 18
    ElseIf oCountry = "Tonga" Then
        oModalAge = 21
    ElseIf oCountry = "Liberia" Then
        oModalAge = 18
    ElseIf oCountry = "Fiji" Then
        oModalAge = 21
    Else
        oModalAge = 18
    End If
   
    ''CurrentAge = AgeYears(#12/31/1985#)
    CurrentAge = AgeYears(Me.DateOfBirth)
    If CurrentAge < oModalAge Then
        MsgBox "Applicant must be at least " & CStr(oModalAge) & " years old!", , "CAVR - ATD": Exit Function
    End If
End Sub
0
Users and privileges :

Hi,
I have a database with this panel:
Customers
Invoices
Products
Users

I need to give privileges to users.  For example:  some users can only access Customers and Products, but not Invoices.  Some users can only see Products.  Some users can access all.

How can I create a table with the users, store the privileges (yes/no) by user and also use this information in the Panel to be able to activate or not the different options according to the privileges?
0
Wanted to import certain cells from an excel worksheet to a table in access:

On Capacity Exel attachment the cells highligthed in orange are the ones I would like imported to the a table called "tbl_SUPPLIER_CAPACITY".  I've manually entered the data from the excel sheet to the table to illustrate what I was hoping for.

See both attachments fo Excel and sample database.
C--Users-lfreund-Documents-EE-Capac.xlsm
C--Users-lfreund-Documents-Capacit.accdb
0
Hello! I have an Access 2016 (via O365) table that is just an email address. What I am trying to do seems like it would be simple, but I am having trouble finding an answer that isn't sql server. I want to query all the results to return just the domain. So if I have a bunch of rows of myemailaddress@mydomain.com (different addresses of course) how can I go about just returning a list of mydomain.com? I have a workaround where I import that field from a flat file to a new table and use the @ symbol as a field delimiter, but I am hoping for a way to query instead. Thanks!
0
Hi Experts,

I have a table named Skilled_Nursing_Visit_Notes, with the following fields

SNV_ID (PK)
Nurse_User_ID_num_SNV Text
Nurse_Signature_Last_Name text
Nurse_Signature_First_Name text

Would like to create a query of all records that have the same Nurse_User_ID_num_SNV  but either Nurse_Signature_Last_Name or Nurse_Signature_First_Name are not the same.
Since it could be due to misspellings, I would have the rule as follows.
If first 4 characters are the same then its considered matching.

(Optional)
However if one of the first 4 characters are not the same, but there is only one character not matching from the entire field and that not matching character is not the first character, then its also being considered the same, as its most likely a misspelling.

Thanks
0
So I have this formula in a excel and was wondering if this could be on my access query design mode as an expression.

The tHREE fields are "NOT ISSUED" and "QTY ON HAND" and "PART NO"

The excel formula is as such:  =IF(E2=E3,"",H2-SUMIF(E2:E2,E2,G2:G2))

Where column E is "[PART NO], column H is [QTY ON HAND], and column G is [NOT ISSUED]
0
From vba in Microsoft Access, how can I open a form saved in a word .docm document and set a textbox on the form to a value?

So far I have:

    Dim objwrd As New Word.Application
    Dim doc As Word.Document
   
    Set objwrd = CreateObject("Word.Application")
    objwrd.Visible = True
    Set doc = objwrd.Documents.Open(FileName:="C:\Users\db1\Documents\Test.docm", Visible:=True)

I know if I'm running code contained in the Word document I can do something like this

Dim frm As New UserForm1
frm.Show

But this code is automation code running in Microsoft Access and I don't see a forms collection in the Document object.
0
I have an Access 2013 small application that I plan to schedule to run at night.  I have a batch file that starts Access and runs a Macro.  That macro runs several custom functions on some tables and exports their results to csv files.  The last part of the Marco is the command "QuitAccess"  

Everything runs correctly, however, On the QuitAccess command, the database is closed but the Access application remains up and running.

I made the location of this access database a Trusted location but it still does not close the application.

Am I missing another piece to this?
0
I'm a bit confused about page level record locking. I have a main form where the underlying table  has 20 fields and a sub form where the underlying table has 200 fields. I know that if the "Page" is not full that the page level locking will also lock the next record(s) but I don't know if this is referring to a collection of records i.e. the main form tables records and the subforms records or just the main form. In the past, to make sure it only locks one record, I have added 5 text fields each filled with 250 random characters. It has worked, never had a main form locking two main records, but I just wonder with the addition of a subform with 200 fields if it is still necessary to do add 5 otherwise pointless text fields
0
Hello - I'm getting "automation error" when I try to execute the File Open dialog code in Access 365, 64-bit (please see attached).

I'm at a loss to even know where to start to troubleshoot this.  Any ideas?

Thanks
atm-err-1.pdf
atm-err-2.pdf
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.