Microsoft Access

219K

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

Good evening,

I have an unbound form with a subform that displays employees who have left the company for whatever reason.  We have to retain sick leave records for 37 weeks after seperation in case we rehire the employee.  The subform displays employees who have been gone 37 weeks or longer.  I have a message box that asks the user if they would like to mark the employee not current.  If they respond yes to the message it marks, in this case, the two employees to not current which is a check box.  I was trying to refresh the subform so that it shows no employees in it once it has been modified.  but the two employees are displayed.  If I leave the form and come back they are gone.  I have tried refresh and requery and i have not had any luck.  My code is below.  Thank you for your assistance.

 On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As DAO.Recordset
Dim Response As String

'    DoCmd.SetWarnings False
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

strSQL = "QInsert_sepinweeks"

Set rs = CurrentDb.OpenRecordset(strSQL)
   
With rs

    Response = MsgBox("Do you want to change the employee(s) to Not Current", vbYesNo)
   
             If Response = vbYes And Not .BOF And Not .EOF Then
   
        .MoveLast
        .MoveFirst
 
   
                  rs.Edit
               
               rs("nc").Value = True
                 rs.Update
'+++++++++
 
         .MoveNext
 
         Wend

 
 …
0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Hi Experts,
I'm having an ODBC failed error message when to run an update in a form.  Below is my code in the Update Button:
 FromBox = 0
    Tobox = 0
    If IsNumeric(BoxHold1) Then
        FromBox = CLng(BoxHold1)
    End If
   
    If IsNumeric(BoxHold2) Then
        Tobox = CLng(BoxHold2)
    End If
   
    DoCmd.SetWarnings False
   
    If FromBox = 0 Or Tobox = 0 Then
        x = DLookup("countofBoxNo", "qryPalletUpdatednotnumCount")
        y = MsgBox("You are about to update " & x & " records. Do you wish to continue?", vbYesNo)
        If y = vbYes Then
            DoCmd.OpenQuery "qryPalletUpdatednotnum"
            MsgBox "Records Updated"
        Else
            MsgBox "Nothing has been changed"
        End If
    Else
        x = DLookup("countofBoxNo", "qryPalletUpdatedCount")
        y = MsgBox("You are about to update " & x & " records. Do you wish to continue?", vbYesNo)
        If y = vbYes Then
            DoCmd.OpenQuery "qryPalletUpdated"
            MsgBox "Records Updated"
        Else
            MsgBox "Nothing has been changed"
        End If
    End If
    DoCmd.SetWarnings True


when I tried to click Update Button, there is a message:
 ODCB-Call Failed.  [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error Converting the nvarchar Value '12496 lester' to a column of data type int.(#245)

I even changed to
 If IsNumeric(BoxHold1) Then
        FromBox = CInt(BoxHold1)
    End If
   
    If …
0
Dear Experts,

I have a combo box on a form with the following date sequence that is pulled in from a table:
2017-2018
2018-2019
2019-2020
2020-2021
2021-2022
etc..

What I would like to do is display as default the value 2018-2019 " IF " the current date is between August 2018 - July 2019 (the school year for us) . Then when the date flips over to August 2019 the default value shown in the combo box will be 2019-2020 until July 2020.

How would I go about doing this?

Thanks in advance!~

Mike
0
In my table, I have a field [tbl Eqp Filters].[Eqp Type]           {string}

In one of the records, the field contains the following data.    
[tbl Eqp Filters].[Eqp Type]   =    TT50-SR12 , TT50-SR12fe

Open in new window

I would like a select statement that could find this record to match on exact matches on this list.

if  strEQPType  = "TT50-SR1" ,  no records would be found.

if strEQPType  = "TT50-SR12 "  then the record the record is found.


The code below not work correctly.

Set rs = dbs.OpenRecordset("Select * from [tbl Eqp Filters] where [EQP Type] like '" & strEQPType & "'")

Any help would be appreciated.
Thank you.
0
Experts,

I am trying to figure out how to use FileDialogFilePicker, but just can't do it. I have a button on a form that I want to allow users to select am image file. I then want that file name entered into a field (ProfilePicName) on the same form. Sounds simple, but I am just too rusty at VBA.

Thanks for any help.

Dale
0
Access vba  
opening excel in the background

Problem: Excel is staying in memory ?

I dont know if i'm  "Dim"ing them correctly  ?

Or if something else.

Dim columnDataStart As Long
 Dim lastColumn As Long
Dim lastRow As Long
Dim rowCounter As Long
Dim firstRow As Long
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim objRange As Object
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object


' Establish an EXCEL application object
Set xlx = CreateObject("excel.application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open(strFileName, , True) ' opens in read-only mode
Set xls = xlw.Worksheets("Template")
    With xlw
     '   .Visible = True
      Set xls = xlw.Worksheets("Template")
  
        With xls
            .Columns.AutoFit
          
            lastColumn = 19
            
            While Len(.Cells(lastRow, 11)) > 0
                lastRow = lastRow + 1
            Wend
            lastRow = lastRow - 1
            For i = 5 To lastRow
                For j = 11 To lastColumn
                ' new code ===========
                    CellDataEvaluate = Trim(.Cells(i, j).Value)
                    CellDataEvaluate = Replace(CellDataEvaluate, "%", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "$", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "#", "")
                    CellDataEvaluate = Trim(CellDataEvaluate)
                    ' check for instring  

Open in new window

0
Hello all:

I have a form that displays some time entry data.  I also have a set of controls that are refreshed based on criteria on the form for the current record. These controls are hidden and then displayed based on the data from another table (basically control parameters for the time entry).  The problem is I can't put it in the Form_Current event because it keeps firing over and over.  Is there another event that fires when you move from one record to another on a form?
0
In Access 2010/2013 VBA I'm using this function as a way to copy files in a foider to another folder - and first time run it works fine - but second time run, if the folder already exists, the target folder is then created inside the already existing target folder - I cannot figure out whats wrong :) ?

VBCopyFolder "H:\Tools\PH\", "C:\Users\Michael\AppData\Roaming\PH", True

Private Const FO_MOVE As Long = &H1
Private Const FO_COPY As Long = &H2
Private Const FO_DELETE As Long = &H3
Private Const FO_RENAME As Long = &H4
Private Const FOF_MULTIDESTFILES As Long = &H1
Private Const FOF_CONFIRMMOUSE As Long = &H2
Private Const FOF_SILENT As Long = &H4
Private Const FOF_RENAMEONCOLLISION As Long = &H8
Private Const FOF_NOCONFIRMATION As Long = &H10
Private Const FOF_WANTMAPPINGHANDLE As Long = &H20
Private Const FOF_CREATEPROGRESSDLG As Long = &H0
Private Const FOF_ALLOWUNDO As Long = &H40
Private Const FOF_FILESONLY As Long = &H80
Private Const FOF_SIMPLEPROGRESS As Long = &H100
Private Const FOF_NOCONFIRMMKDIR As Long = &H200


Public Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Long
    hNameMappings As Long
    lpszProgressTitle As Long
End Type

Public Sub VBCopyFolder(ByRef strSource As String, ByRef strTarget As String, bOverWrite As Boolean) ', bAllowUndo As Boolean)
    Dim op As SHFILEOPSTRUCT
    Dim lngFlags As Long

    With op
        .wFunc = FO_COPY
      

Open in new window

0
How can I set UseAccessSpecialKeys on/off with VBA?

Thanks in advance.
0
I need to be able to find a Word document on a web domain, edit it and then save those changes, ideally without downloading and then uploading. Is this possible?

So within Access I will select a file from a table that has that file's exact location, such as "www.mydomain.com/files/myDoc.docx". Once I have selected that file, the Word document will open, and then I will make my changes. Still in Word, I will then Save the document and it will be saved in its original place.
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have a TotalCost field on a form that stays blank until a selection of items has been completed. If insufficient items or an incomplete selection of items is chosen I want the total price field to be blank to indicate that the selection is not complete. Sometimes a complete selection is not possible (awaiting further information for example) but I still want to show a total on a  report, what has been selected and the accumulated cost so far but show the items on the report that are not complete either with a blank or a Zero. I then need a Grand Total. IsNull does not neither does NoneZero
0
I have installed an MS Access .accde in C:\Program Files (x86)\Contacts and when I run it I get the message 'A potential security concern has been identified.'

How do I overcome this?

Thanks in advance.
0
I would like to get some assistance with dynamically changing a form's **multi-select** property through VBA (without having to close/re-open the form).

Current process:
- I have one listcontrol ("ListBoxActions") with 4 tabs on a form
- In the listbox's property sheet, the value for **multi-select** = "None"
- Upon clicking tab 1, 3, and 4, I would like to have the multi-list property set to "1" (single).
- Alternatively, upon clicking tab 2, I would like to dynamically change the multi-list property to "2" (extended).

I have tried two approaches (see below):
           Me.ListBoxActions.MultiSelect = 2   ' Extended

Open in new window


and/or

           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 2   ' Extended

Open in new window


Unfortunately, neither of the two methods work.   That is, once I click on tab 2, I only can select a single value (vs. multiple values) from the listbox.  

Below is the full code for the listbox.   What am I missing?   How should the VBA be modified to allow dynamic switching of the multi-select property?

Thank you,
EEH

Private Sub TabControlObject_Change()

    Select Case Me.TabControlObject.Value
               
        Case 0
           MsgBox "1st tab"       
           'Me.ListBoxActions.MultiSelect = 1
           Forms("frmLogin").Controls("ListBoxActions").MultiSelect = 1 'Simple
           Me.ListBoxActions.RowSource = "SELECT tblTabControlRowSources.RowSource_Page_1 FROM tblTabControlRowSources WHERE

Open in new window

0
Hi everyone. I'm designing a database app by using Microsoft Access as a front end (FE) to a MS SQL Server backend (BE). I want to optimize performance as much as possible. Currently, I'm using linked tables. However, for some of my queries, I use pass-through queries and it returns its results instantly. As I have observed, I can still use pass-though queries without linking the tables in the BE. The problem is that the results of pass-through queries are read-only.

Presently, for adding/editing records, I build those queries by basing them on the relevant linked tables. Sometime, I add records by building the query as an Append Query (INSERT INTO).  Is there any equivalent of a pass-through query that allows me to add/edit records? Please note that I will like to accomplish this using either the Query Design Grid (QDG) or by writing pure SQL in the QDG.

Thanks.
0
I need to run a query where I count then group by whether a field name has particular set of characters.

So here are the sample field values:
AAR_GUAM HS_2018-07-20(Gibson)1.3(inf)
AAR_KADENA ES_2018-07-19(Johnson,Gibson)
AAR_KADENA ES_2018-07-26(Gibson)1.1-1.5-1.7(inf)
AAR_KUBASAKI HS_2018-07-26(Gibson)

I have the query for counting the number of AAR reports if the name (for example Gibson) appears in the report name.
SELECT tblAAR.AARFileName, Count(*) AS AAR
FROM tblAAR
WHERE (((tblAAR.AARFileName) Like "*" & [TempVars]![tvUserLname] & "*"))
GROUP BY tblAAR.AARFileName;

Open in new window


But I need to separate out those files names that "(inf)" and those that don't and create a separate count for both of them. A little stuck on how I can do this...

Any help would be appreciated!

Thanks!
0
Experts,

I've been away from working for a year or so and am very rusty. I am helping a non-profit group by building them an Access database. They have been around for 5 or 6 years and are working with kids in Africa by arranging for sponsors to send the kids to school. They have been keeping up with everything in an Excel file. They have over 100 kids now and just can't keep up with it in Excel very well.

I've got a form with subforms created that shows all of the information needed on each kid and want to also show the picture of each kid. I can't find a solution on EE that answers what I am looking for. The database will be accessed via Remote Desktop Connection. I was thinking I could store the images in a folder and then show the image on the form. I just don't know how. I know I would need to provide the path and file name, but just can't figure out how to put it all together. By the way, I never have been very good at VBA, but am able to figure out a few things.

Thanks for any help, Dale.
0
Hi Experts,
I am looking for a script that will read all files of a specific folder (.txt,.csv) and count how many records they're in total.
all files have fields header, however some files only contains the header and should not be count.
Thanks in advance.
0
I am using the command DoCmd.Outputto  and I need to know how to complete the command to identify the individual's name (I have it in a text box on the form + "_DailyReport_" & Todays Date

Docmd.OutputTo acOutputQuery, "qPrintDaily", acFormatXLSX,  Me.User & "_DailyTaskReport_" & Date()

Tried doing it this way but keep getting errors.  How can I make this work?
0
Hi Experts,
I post my question few days ago and tried whatever the suggestions from other experts, but it seems still having problem.  I changed some of my code, it gave me an error message "too few parameters, expected 2".  I could not figure it out it error.   ( I would like to do is when the client has two different Employers, for example, Mr. Smith works for ABC Market and TLC Inc.  I would like to print out 2 copies of ABC Market at the same time then follow by 2 copies TLC Inc instead 1 copy of each Employer each time and users have to sort them after both employers print out.  Below is my code:

Dim stDocName As String
Dim rs As Recordset
Dim db As Database
Dim stCriteria As String

EmployerCodeID = DLookup("EmployerCodeID", "qryNoticeLetter/CoverLetter")   ' EmployerCodeID is numeric
    Set db = CurrentDb()
      stCriteria = "Select * from [qryNoticeLetter/CoverLetter] where EmployerCodeID = '" & Me.EmployerCodeID & "'"
    Set rs = db.OpenRecordset(stCriteria, dbOpenDynaset, dbSeeChanges)
    If rs.EOF Then
    Else
    If Form_frmPrintedFormsAll.LabelMessage = "Notify Client" Then                  
         stDocName = "rptNoticeLetter/CoverLetter"
          DoCmd.OpenReport stDocName, acPreview
          DoCmd.SelectObject acReport, stDocName, False
           DoCmd.PrintOut acPrintAll, , , , 2
          DoCmd.Close acReport, stDocName
    Else
        stDocName = "rptCoverLetter"
        DoCmd.OpenReport stDocName, acViewNormal
           …
0
Firewall Management 201 with Professor Wool
Firewall Management 201 with Professor Wool

In this whiteboard video, Professor Wool highlights the challenges, benefits and trade-offs of utilizing zero-touch automation for security policy change management. Watch and Learn!

I have a table in an Access 2007 database that has a bunch of different statuses for one row of information. I am trying to match this table's data to another table which separates each status into a different row. Now, my original plan was to separate the first table's records into different rows based on their statuses and append them to a new table that can be used to match data with the original table. That is, I would create a query that focused on one status and assign it a letter that was associated with the status, such as "P" for pending. Then, I would append that data, with the letter replacing the original status column, into a new table that could be used to match data with the second table.

The only problem with this plan is that it would require multiple appending tables. I'm wondering if anyone out there has made a query that could break a row of data into separate rows and assign them data so that I would only have to build and append one query instead of several separate ones. If not, does anyone have any suggestions as to how I could make a single query that could do all this work? Or am I better off building multiple appending queries like my original plan?

Please let me know if my explanation was unclear and you need more information!
0
Hi Expert!

Just a small help!

I have seen a code for SQL table re-linker here see below:

Dim sConnect As String
sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDB
Set tdf = dbs.TableDefs("YourTableName")

tdf.Connect = sConnect


Now I have 64 tables to be re-linked as you can see above there is one provision for a table only(Set tdf = dbs.TableDefs("YourTableName")
), then how do I accommodate all the 64 tables there on MS Access start-up form Open event??????????


Regards

Chris
0
I have got a form that is loading records to a table by endusers.  Each time a new record is created I would like the new record StartTime field to reflect the previous record EndTime value.  At the end of the day all records are loaded to the main table for reporting purposes soooooooooooooo all of the todays records are for today's date.

Currently, the EndTime is reflected in the StartTime field and I need it to be reflected every time the user enters a new record.  How can I code that to always put the EndTime of the previous record in the new record StartTime field?
0
Experts:

I need some help (in Access) where selecting a list box value will open a query with the selected list box value being the ** field name ** (not the query criteria).

Please find attached sample database which contains the following objects:
1. tblFactors (with fields "Age", "Gender", and "Race")
2, Query2 -- executing the query will result in six (6) records with fieldname = [Listbox Field].   Thus, the query should only be executed via the "frmLogin"
3. frmLogin -- includes a listbox with "Age", "Gender", and "Race" being hardcoded

Current Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it now shows 6 records and all of them show "Age"... this doesn't work for me!

Envisioned Process:
1. Open up frmLogin
2. Click on, e.g., "Age"
3. Step 2 results in Query2 being executed... it **should** now should the field [Age] and six records (i.e., 25, 41, ..., ..., 24).

My questions:
1. How should the VBA (in frmLogin) be modified to pass the listbox value as a field name into the query?   How should be VBA be modified to show, e.g., both [Age] and [Race] in the query assuming both were selected in frmLogin?

2. Question #1 is the most important one at this time.   However, ideally, I also would like to have a solution that accounts for having, potentially, up to 150 field in tblFactors.   If so, I would like the listbox to dynamically include all available fields in the listbox.   Is that possible?…
0
I have a subform with 10 date fields on it.  And I want to insert the values of those dates fields into a table named tblDeliverydates using INSERT.  This isn't right.  Can someone help?

    Dim DeliveryDate As Date

    INSERT INTO tblDeliveryDates (DeliveryDate) VALUES (DelDate1, DelDate2, DelDate3, DelDate4, DelDate5, DelDate6, DelDate7, DelDate8,ReskedDate9, ReskedDate10)

Open in new window


Note:  If it matters, sometimes one or more if the date fields may be empty.
0
I've been asked to do some research on what the advantages are by moving an MS Access front-end to a .net platform. We have a front-end that runs on a SQL backend currently with about 15-20 users. I've done some research and for this size user base & database I really can't seem to find any major benefits by moving the front-end to .net. Can anyone shed some light on if this makes sense or not? I seem to think it doesn't because of the size of the database and the amount of users but I'm open to opinions.
0

Microsoft Access

219K

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.