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

In Access 13, I want to use bold text for a section of a control - but the bold codes seen below are simply ignored - italics works fine - very strange. The control is of course set to Rich text :)

="Part 1 <i><b> Bold and italics </b></i> Part 2"
0
Please excuse the lack of knowledge and possibly wrong verbiage used in this question. My customer has an old access database from 1997 and its been converted to a 2002-2003 format. Now my customer would like to convert this .mdb file to the .accdb format. I have tried everything on google and nothing is working. Is there something I am missing. Be advised, I have no CLUE about Access 2016 or any other version.
0
Hi

I have the two Access tables shown in the image. I want to update the LicenceNumberFK field using the correct values in the second table that I imported from Excel t_Arms2. I want to do this by looking up the LicenceNumberFK field in t_Arms2 using the ArmPK field

1
0
Hello Experts:
We have a departmental Access database in the .MDB format that's been hovering around 1.6-1.7gb in size. The database is exhibiting unusual behavior in that as of a few days ago, it began frequently crashing and creating an automatic backup. There are 1,200 objects in this database...would it help if we created a new shell and imported all of the objects into the new database?
0
Hi

I have a split database Access solution for which I have code that automatically links the front end to the backend. When I run this code I am getting the following error. I do not have a link for the table mentioned. I never created such a table

Thanks

1
0
Hi

I have a text box called LicenceNumber that is populated programmatically from another form using the following code. What is the best even behind the text box LicenceNumber that will be triggered when text is changed from the other form?

 Forms(oNewLicenseeFor).Controls("LicenceNumber") = Nz(DMax("LicenceNumberPK", "t_Licence"), 0)
0
I have two excel sheets come from two different sources ( each with about 15 fields  - say field 1, Field2.... Field15 ) .
There will be some  differences  in data , and I need to show  the differences ( probably with a different color)

It can be done with MSAccess or Sql Server or Oracle as long as I get the results
0
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
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 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
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

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.