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

Experts:

I have a Close button on sbfrmEndUser and when I click it is closes the parent but I need it to close the sbfrmEndUser

How can I reference this?  

Parent: frmContacts
Subform:  sbfrmContacts

Thank you,
0
Hi Experts,
I have a problem with disable Feedback screen in Office 365 Access 2016 run time version.  whenever user has one database opened and try to open another database then there is a feedback screen in the background, how do I removed or disable it?  or it's the default in 2016 run time when open up two different database?  Attached is the screenshot.  

Thank you,
ScreenShotRuntime1.jpg
0
I want to push a button called "Save". This will run a query that will be eventually create a report/pdf.
For now, I'm just working on the query that will select the record that I need. I'm trying to run a query based on a selection in a form/table.

At any give time I will have a record in my QREVALE form/table. The field is called TagNumber.
Below is a query that I have that does NOT call a special record. I want this query to only call the record that has the matching TagNumber from the form.

any suggestions?


SELECT 
SkpiUpdate.Date, 
QREVALUE.TagNumber, 
QREVALUE.QPRQPINumber, 
SkpiUpdate.NAMC, 
FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag;

Open in new window

0
I am looking for a regex pattern to  return true for these two string
("SELECT * FROM CIM_DATAFILE WHERE NAME = '" & Replace(sValue, "\", "\\") & "'")
strC = "'" Then


and false for ' "wwwww_0" or anything else

in vba
0
Hi. I have two forms. The first loads an image in the following event. The second has a button where the image can be edited. My problem
is that when I do this and go back to the first form the old image is there. I have tried to reload the image and refresh the form but it doesn't work


Private Sub Form_Current()
   Dim oDB_Folder As String: oDB_Folder = CurrentProject.Path
    Dim oSavedPath As String: oSavedPath = [PhotoFile]
    Dim oActualPath, F As String
    F = Replace(oSavedPath, "DatabaseFolder", oDB_Folder)
   Me.imgPhoto.Picture = F
End Sub

Open in new window


Private Sub btnSelectPhoto_Click()

    Dim strFile As String
    Dim PhotoFile As String
    strFile = GetFileName(CurrentProject.Path)
    If strFile > "" Then
        PhotoFile = Filenm(strFile)
        If Filepath(strFile) <> CurrentProject.Path & "\Media\ID Photos\" Then
            FileCopy strFile, CurrentProject.Path & "\Media\ID Photos\" & PhotoFile
        End If
        imgPhoto.Picture = CurrentProject.Path & "\Media\ID Photos\" & PhotoFile
        Me.PhotoFile = "DatabaseFolder" & "\Media\ID Photos\" & PhotoFile
        Form_f_LicenseeDetailsEdit.PhotoFile = "DatabaseFolder" & "\Media\ID Photos\" & PhotoFile
        Form_f_LicenseeDetailsEdit.Refresh
        Form_f_LicenseeDetailsEdit.Repaint
    End If
    
End Sub

Open in new window

0
Experts,

I am opening frmProjects_Extended from another form called frmProjects.  
I am getting a msg box for Trade_No.  I dont know why.  
See anything wrong?  Thank you....

frmProjects:
Private Sub txtTrade_No_Click()

            DoCmd.OpenForm "frmProjects_Extended", , , , , acDialog, OpenArgs:=Me.Trade_No

End Sub


frmProjects_Extended:

Private Sub Form_Load()

      If Nz(Me.OpenArgs, "") <> "" Then
'         'If Not Me.NewRecord Then
            Me.Filter = "[Trade_No] = " & Me.OpenArgs
            Me.FilterOn = True
       Else
     
     End If
End Sub
0
Hi. I was given the following Access VBA code to rename a backend column in a split Access database solution. What similar code would I use to rename a table? Thanks

Public Sub RenameColumn(ByVal tableName As String, ByVal oldName As String, ByVal newName As String)
    Dim dbName As String
    dbName = GetLinkedDBName(tableName)
   
    Dim db As DAO.Database
    Set db = OpenDatabase(dbName)
   
    Dim tdf As DAO.TableDef
    Set tdf = db.TableDefs(tableName)
    If (ExistInCollection(oldName, tdf.Fields)) Then
        Dim field As DAO.field
        Set field = tdf.Fields(oldName)
       
        field.Name = newName
    End If
End Sub

Public Function ExistInCollection(ByVal key As String, ByRef col As Object) As Boolean
    ExistInCollection = ExistInCollectionByVal(key, col) Or ExistInCollectionByRef(key, col)
End Function

Private Function ExistInCollectionByVal(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    item = col(key)
    ExistInCollectionByVal = True
Exit Function
Error:
    ExistInCollectionByVal = False
End Function

Private Function ExistInCollectionByRef(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
    Dim item As Variant
    Set item = col(key)
    ExistInCollectionByRef = True
Exit Function
Error:
    ExistInCollectionByRef = False
End Function
0
I am trying to use the SQL Migration Assistant to upgrade an Access database to SQL.  I cannot get the Migration Assistant to connect to the SQL.  I get this error no matter what user password combo I try: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I am using SQL Express and when I use SQL Management Studio I can log in with no problem.

This is not my forte at all so I know I am missing something simple.
0
Hi

I am using the following Access VBA code to load a list of forms to a Combobox
How do I load the form Captionto an additional ComboBox?


Sub oLoadFormsToCombo()

On Error GoTo EH

    Dim frm As Object
    Dim I As Integer
    
    For I = cmbFormsAndReports.ListCount - 1 To 0 Step -1
        Me.cmbFormsAndReports.RemoveItem (I)
    Next I
    
    Me.cmbFormsAndReports.SetFocus
    Me.cmbFormsAndReports.Text = ""
    Me.cmbFormsAndReports.AddItem ("")
    For Each frm In CurrentProject.AllForms
        Me.cmbFormsAndReports.AddItem (frm.Name)
    Next
    
Exit Sub
EH:
MsgBox "Error loading forms: " & Err.Description
    
End Sub

Open in new window

0
Hi
I found  the following article on storing rich text in an Access table. Is there a way to build a form with controls that can be used to format this text in bold, italic etc or do carriage line returns etc

https://support.office.com/en-us/article/create-or-delete-a-rich-text-field-9f86237d-dbbc-4a85-b12c-9d8dca824630#bkmk_whatis
0
I am trying to create a query in Access 2016 that will remove duplicate entries from an access table, based off 1 field and regardless of if the other fields are the same or different. I have tried to use different query methods including nested and I can't seem to get it quite right.

Here is an example of the database as well as the results I hope to achieve. There are multiple types of records, single (keep all), duplicate ID with the same name (keep the newest), and duplicate ID with a different name (keep the newest).

Existing Database
ID      FirstName  LastName  Date
111AAA  John       Snow      1/1/2020
111BBB  Arya       Stark     1/1/2020
111BBB  Arya       Stark     1/1/2019
111CCC  Eddard     Stark     1/1/2020
111CCC  Sansa      Stark     1/1/2019

Open in new window


End Result
ID      FirstName  LastName  Date
111AAA  John       Snow      1/1/2020
111BBB  Arya       Stark     1/1/2020
111CCC  Eddard     Stark     1/1/2020

Open in new window

0
Hi

I want to store html in my Access table so that it can be used to load a text box that is formatted as rich text

I have been asked if it is possible to use the  HTML editor which is built into Word to create the html for this

Thanks
0
Hi. I have been asked to do the following by a client on my Access forums. Is this viable?

"Please investigate adding scroll bars to large forms. On a standard 13-inch laptop some are so tall that they hide the bottom row of buttons. BUT we won’t go ahead if it’s difficult or time consuming."
0
I have the following code in several onclick events on a form.  But it seems redundant to keep repeating the code for each onclick event.  How can I use this code just one time and call it from each onclick event?

    If Nz(Me.txtPreferredName, "") = "" Then
        MsgBox "Enter selection in 1stField!!!"
        Me.txt1stField.SetFocus
        Exit Sub
    Else
        If Nz(Me.cboCampus, "") = "" Then
            MsgBox "Enter selection in 2ndField!!!"
            Me.cbo2ndField.SetFocus
            Exit Sub
        Else
            If Nz(Me.txtLastName, "") = "" Then
                MsgBox "Enter selection in 3rdField!!!"
                Me.txt3rdField.SetFocus
                Exit Sub
            Else
                If Nz(Me.txtFirstName, "") = "" Then
                    MsgBox "Enter selection in 4thField!!!"
                    Me.txt4thField.SetFocus
                    Exit Sub
                Else
                    If Nz(Me.cboClass, "") = "" Then
                        MsgBox "Enter selection in 5thField!!!"
                        Me.cbo5thField.SetFocus
                        Exit Sub
                    Else
                        If Nz(Me.txtPhone1, "") = "" Then
                            MsgBox "Enter selection in 6thField!!!"
                            Me.txt6thField.SetFocus
                            Exit Sub
                        Else
                            If Nz(Me.cboCycle, "") = "" Then
                …
0
I am trying to write a Delete query on MS/ACCESS using the provided GUI. I have the table from which I want to delete records and a query with the keys to the records to be deleted - the attacvhed screenshots provide the background for the situation.
However, I am getting the error "Specify the Table containing the records you want to delete".
What am I missing??

can anyone help?
Screenshot-2020-01-30-at-18.02.07.png
Screenshot-2020-01-30-at-18.05.32.png
0
Hi Experts,
I have a question about report design in access 2016.  I have a report to show the total of the Order and shipped from 1/1/2018 to 1/31/2020, we need to compare the total of each year.  The report is order by year then each month (from January to December), is anyway I can change the design to show both years side by side in one page?  instead showing each year in one page, I want to show two years side by side so that I can see the difference between 2018 and 2019 or show all three years on a report if possible, otherwise at lease show two years in a report. so the format will be look like below:

First Page of the report

Year     Month
  2018                                                                               2019
         January                                                                              January
                       Total of Order: 200                                                        Total of Order: 200
                       Total of shipped : 150                                                    Total of shipped: 90
       
         February                                                                          February
                       Total of Order: 300                                                        Total of Order: 300
                       Total of shipped : 150                                                    Total of shipped: 30

       December                                    …
0
Hi Experts,

We are running out of space on the Servers C drive, what does entails to move the LDF files over from the C to the E drive of same server?

We have Access applications linked to those SQL databases, do we have to do something about them as well?

Thanks
0
Experts,

I am importing an excel file XLImportToAccess and comparing with tblImportToAccess (production table).
It works fine.
I have to add additional criteria though.
If XLImportToAccess.DATE doesnt equal tblImportToAccess.DATE and all other fields match as shown in the append query below, then I need to update tblImportToAccess.DATE to the date found in field XLImportToAccess.DATE.  

I hope that makes sense.  Please let me know if I need to add something else.  

INSERT INTO tblImportToAccess ([Trade #], [Buy CP], [Quantity BBLS], [Data], Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes)
SELECT XL.[Trade #], XL.[Buy CP], XL.[Quantity BBLS], XL.[Data], XL.Batch, XL.[Origin / Deal], XL.[Date], XL.Grade, XL.State, XL.[Trade #1], XL.[Sale CP], XL.[Operation #], XL.WorkingDate, XL.SentAwayDate, XL.Notes
FROM XLImportToAccess as XL LEFT JOIN tblImportToAccess as I2A
ON XL.[Trade #] = I2A.[Trade #]
AND XL.[Buy CP] = I2A.[Buy CP]
AND XL.[Quantity BBLS] = I2A.[Quantity BBLS]
AND XL.[Batch] = I2A.[Batch]

AND XL.[Date] = I2A.[Date] 
WHERE I2A.ID IS NULL

Open in new window

0
Hi. I have an ms/access database that has a table from which I need to delete records according to specific criteria.

This is [myTable] with a key, [myTableKey]
 I have a set of analysis, running from other tables that yield keys [myTest1Key], [myTest2Key], ... [myTestNKey]. I need to delete the records from myTable  where [myTableKey matches the [myTestiKey], so the [myTable] is totally cleaned out.
How do I do it?
0
I receive this error when I am trying to select an item from a multi-column List Box.
"The value you entered isn't valid for this field"

I am not trying to insert anything into a field.
0
Is it possible to get assistance on converting the CRC  Code below which was done in C++ to MS Access VBA


unsigned short int cal_crc(unsigned char *ptr, unsigned int len)
{
unsigned char i;
unsigned int crc=0;
while(len--!=0)
{
for(i=0x80; i!=0; i/=2)
{
if((crc&0x8000)!=0)
{
crc*=2;
crc^=0x18005;
}
else
{
crc*=2;
}
if((*ptr&i)!=0)
crc^=0x18005;
}
ptr++;
}
return(crc);

Open in new window

0
I've converted a text field (ActiveCustomer) parsing out a CustomerNumber, but the result gives me an error when i try to filter on the value.
I used Val() to convert string to a number and the result  'looks' like a number (right justified) on the screen.

The error is "data type mismatch"

Access Query:
SELECT CGEQUP_Base.StockNo, CGEQUP_Base.ActiveCustomer, CGEQUP_Base.InventoryStatus, DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoStartDate, Date()-DateValue(Left([CGEQUP_Base].[ActiveCustomer],6) & "20" & Mid([CGEQUP_Base].[ActiveCustomer],7,2)) AS DemoDays, CGEQUP_Base.EquipmentModel, CGEQUP_Base.EquipmentMake, Val(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))) AS CustomerNumber
FROM CGEQUP_Base
WHERE (((CGEQUP_Base.ActiveCustomer) Like "*DEMO*") AND ((CGEQUP_Base.InventoryStatus)<>"V") AND ((Int(Trim(Mid([CGEQUP_Base].[ActiveCustomer],InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1),InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-InStrRev([CGEQUP_Base].[ActiveCustomer]," ",InStrRev([CGEQUP_Base].[ActiveCustomer]," ")-1)))))<>99));

My results look good if I remove the filter, but I need to remove all records with customer number of 99

0
Hi. I am trying to use the following code to pull a date from an Access table and count whether the combination of it and first and last name exist. The count comes up as zero. I am pretty sure this is some issue with dates but I am not sure what

1
   Dim DOB as date
oFirstName = DLookup("FirstName", "t_Licence", "LicenceNumberPK=" & oLicenceNo)
    oLastName = DLookup("LastName", "t_Licence", "LicenceNumberPK=" & oLicenceNo)
    DOB = DLookup("DateOfBirth", "t_Licence", "LicenceNumberPK=" & oLicenceNo)
   
    Dim oCountMatch As Integer
    oCountMatch = DCount("*", "t_Permit_Rejections", "DateOfBirth = #" & DOB & "# And FirstName = '" & oFirstName & "' And LastName = '" & oLastName & "'")
    If oCountMatch > 0 Then
        MsgBox "There is a Permit Rejection with the same first name, last name, and Date of Birth", , "CAVR - ATD"
        Exit Sub
    End If
0
Hi experts,


How can I combine multiple but unique records into one record in Access 2016?
Each record has about 30 fields.
I know something about queries but have no idea how to merge data.

Looking forward to a solution.


Cheers
0
I'm trying to import two different worksheets from an Excel file.  When I set the 'range' = to the worksheet that I want, it still brings in the first worksheet.

1st worksheet name = "Functions"
3rd worksheet name = "Role"

The frmTab variable is set to the worksheet name.

Here is the code:                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, frmTab, GetFile, True, frm_Tab

???

Thanks for your help,
Jeanne
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.