Microsoft Access





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.

make all objects on a form disabled on a form except the object/checkbox  I'm clicking on..

I have checkboxes/combo/textboxes  buttons  on a form.,


When i click on a checkbox  "chkbox1"

I need all other objects on the form disabled or something except for        "chkbox1"  ?


I have an OpenArgs statement with 2 criteria
but I need to add a 3rd: Nz(Form_subfrmProjects_Extended_List.Batch_No, "")   .....Its text
I have been trying to do this but its out of my skillset.  

Thank you for your kindness.  

Dim MyArgs As String

MyArgs = Nz(Form_subfrmProjects_Extended_List.Buy_CP, "")
MyArgs = MyArgs & ";"
MyArgs = MyArgs & Nz(Form_subfrmProjects_Extended_List.Trade_No, "")

    DoCmd.OpenForm "frmLetterOfCredit", , , , , , MyArgs

Open in new window

then on the On Load of the form:

 Dim ArgsSplit() As String 'array to hold the string we've sent in
    If Nz(Me.OpenArgs, "") = "" Then
         Exit Sub 'no arguments were sent in so bail
    End If
    ArgsSplit() = Split(Me.OpenArgs, ";") 'two values, positions 0 and 1 holding strings you want to use
    If ArgsSplit(0) <> "" Then 'there was a first argument, Chr 34 is code for double quotes
        Me.Filter = "[Buy_CP] = " & Chr(34) & ArgsSplit(0) & Chr(34)
    End If
    If ArgsSplit(0) <> "" And ArgsSplit(1) <> "" Then 'two arguments
         Me.Filter = Me.Filter & " AND [Trade_No] = " & Chr(34) & ArgsSplit(1) & Chr(34) 'add on the second WHERE condition
    End If
    If ArgsSplit(0) = "" And ArgsSplit(1) <> "" Then 'only second arguments
         Me.Filter = "[Trade_No] = " & Chr(34) & ArgsSplit(1) & Chr(34) 'Just add the second WHERE condition
    End If

    Me.FilterOn = True

Open in new window


My application will generate PDF's.     Those PDF's will almost certainly be always saved the same chosen folder.   But a different user may want to choose another folder.
So, what is the best way to save the file path (string)?

1) I could store the path string to a constant.    This is OK for me, but not for a non-programmer.  
2) I could create a  table with one record and one field.      If I wrote code around that, then the other users could change & store the information.  
3) I could create XML file.    But now, I would have to do some coding to handle a situation where the XML file was not there  (for various reasons).

Is there a better solution?
I've imported a set of records into a table from a software inventory application. It has some useful information, e.g. computer/server (field is called 'computer'), software name (field is called 'name'), publisher, version etc.  The records are basically formatted in a row for each piece of software installed on each computer, so for e.g.

•      Computer1 – software1
•      Computer1 – software2
•      Computer1 –software3
•      Computer2 – software1
•      Computer2 – software2
•      Computer2 – software3
•      Computer3 – software1
•      Computer3 – software2
•      Computer3 – software3

What I was hoping to do is identify any computers/servers which do not have a specific piece of software installed upon them (when they should) using an access query, so I'd specify the actual software name somewhere in the clause and it  produce a unique list of computers servers which do not have that software installed on it. I'm trying to identify the best way of doing this initially as there are a few apps I am particularly interested in.

And then whether I could get a count for each unique piece of software in the data - a list of corresponding computers/servers which do not have that piece of software installed upon them. Again the key columns would be computer and name.

So it would produce:

•      Software1 – not installed on computer1, computer8, computer15 etc.
•      Software2 –not installed on computer5, computer22, computer 86 etc.
I have a continuous form in my Access 2013 application.  Each line is pre-populated with values (Year, Principal, Interest, Penalty, Lien Fess and Atty Fees) from the bound record.  The bound fields are locked, no entry permissable.
Each line also has bound fields (NewPrincipal, NewInterest, etc..) pre-populated with the same values.

The user is permitted to change any of the bound values.

If the user changes any values in the 'New' fields I would like the background of that line of the continuous to be yellow.  There can be many lines on the continuous form and not all will be changed.  By making the background of any changed records yellow it would be easier for the users to keep track of which records had already been updated.

I need to append to tblProjects if [Trade#1] is NOT IN [Trade No].
I am not sure if my query is correct.
If it is correct, when I execute it  it says I can not add records due to type conversion failure and key violations.

this is my query:
INSERT INTO tblProjects ( [date], Trade_No_2, Trade_No, Buy_CP, Quantity_BBLS, Batch, Origin_Deal, Sale_CP )
SELECT XL.Date, XL.[Trade #1], I2A.Trade_No, XL.[Buy CP], XL.[Quantity BBLS], XL.Batch, XL.[Origin / Deal], XL.[Sale CP]
FROM XLImportToAccess AS XL LEFT JOIN tblProjects AS I2A ON XL.[Trade #1] = I2A.Trade_No
WHERE (((XL.Date) Is Not Null) AND ((I2A.Trade_No) Is Null));

Open in new window

I have attached the db. There are only 2 tables and 1 query.  If you could kindly take a peek at it and let me know I would appreciate.
Hello Experts

I have a standard routine which I use to create a linked table in Access and to link it to a comma-separated value file.

I have been using it for a long time and usually it works beautifully. Today, however, I needed to link to some files whose names contained additional dots to the one that introduces the csv file suffix.

The relevant bits of are: (VBA classic):

      Dim db as DAO.Database
      Dim tbl as DAO.TableDef

      Set db = CurrentDb()
   ' create link
   Set tbl = db.CreateTableDef(strLinkName)
   tbl.Connect = "Text;DATABASE=" & strTextFileFolder & ";TABLE=" & strTextFileName   ' e.g. Text;DATABASE=C:\quick;TABLE=descs.2.csv
   tbl.SourceTableName = strTextFileName                                              ' e.g. descs.2.csv
   db.TableDefs.Append tbl		' error occurs here when append attempted if file name has extra dot(s)

Open in new window

To reproduce the problem, I created two files in my C:\quick folder, containing identical CSV data. One file is named descs2.csv, the other is descs.2.csv.

Using the code shown, I can create a link to descs2.csv, but NOT to descs.2.csv. The latter gives an error 3011, saying that the Microsoft Access database engine could not find the file
'descs.2.csv'. Make sure the object exists ... etc.

Attempted workarounds I have tried include :
      surrounding the file name after the TABLE= with [square brackets]. Gives the same error, right down to the file name.
        putting [] round the name of the file in the tbl.SourceTableName attribute. Gave an error 3025 - invalid string.

Does anyone else have a solution for this, or am I flogging a dead horse here?

Thanks in advance

Hopeful Kiwi
Is there an easy way to show a progress bar in MS Access.  When I click on a button I want a pop up that runs for maybe 3 to 5 seconds.

To give you some background perspective... O the click of the button, a form and several subforms open... but this is taking about 5 seconds and the user does not know what is happening.  There I want some progress bar so the user knows that the forms are being loaded.

thanks much!

I have two tables tblLicences  and tblLicences2. The structure is exactly the same in both. The first column is called LicencePK. How do I append only the records in tblLicences2 where the LicencePK is higher than the highest in tblLicences to tblLicences


I am importing from excel and sometimes I need to add a CoName to the db as that company has not been added yet.  I have to manually add that CoName to tblCompanies and its starting to be a real pain.  I think what I am after is an INSERT INTO tblCompanies.CoName when the excel file company_name is NOT IN tblCompanies.  I do not know how to first check if the company name is contained in tblCompanies.CoName first and then automatically add that excel file company_name into tblCompanies.CoName

I hope that makes sense.  Let me know if I have missed something.  

thank you
I had to create a new CD.mdb database due to errors in the index.
I imported all tables, forms, queries, etc. successfully.  No index error found while compacting database.

I replicated the database to three other users, while keeping the design master on my computer.

When the other users open their replica, as error appears:   Missing or broken reference to OWC10.DLL.

How can I fix this problem?

Here are 2 images:

 Image 1
Image 2
I hope to resolve this before the users return to work on Monday.

Thank you for your help!
I have a Access table (tblExcelProductivity) that includes FullName, FieldOffice, Mnth , and Goal. I would like to use an update query to update the Maximum Goal number with 0 for each like record based on FullName, FieldOffice and Mnth. For example below, Mary has 2 records in Arlington in the month of DEC with Goals of 45 and 67.2. In this case 0 would be updated 67.2 the max for the 2 records.


I'm using the following code and receive:

UPDATE tblExcelProductivity INNER JOIN tblAdjDupGoals ON (tblExcelProductivity.FieldOffice = tblAdjDupGoals.FieldOffice) AND (tblExcelProductivity.FullName = tblAdjDupGoals.FullName) AND (tblExcelProductivity.Mnth = tblAdjDupGoals.Mnth) SET tblExcelProductivity.Goal = 0
WHERE (((tblExcelProductivity.Goal)=Max([tblExcelProductivity]![Goal])));

Open in new window

Experts, in query design, I need to subtract 2 days from a [Window_Date] but but the count does not include the weekends (Sat Sun).  Only work week days are counted.  How could a query be developed for this criteria?

thank you
I have a form that is in datasheet.  I open the form and move a field from the far right to the far left and save the form but when I open it up that field I moved from the far right to the far left is still in the far right.  I save the form when I get the prompt (I made other edits to the form to be prompted for a save as simply dragging the column doesnt prompt a save) but it doesnt save the ordering of the fields.  I want to be able to see this field as it hidden out of sight if on the far right.  I seem to have this issue a lot and I dont know how to save the forms ordering.  I dont think its the tab order as that is only for tabbing.  

thank you.

I need to update tblLetterOfCredit.Amount to tblPricing.[Calc Price] where tblLetterOfCreditID.LetterOfCreditID = me.LCID
I am calculating a price on tblPricing.[Calc Price] and have a button that if clicked will update tblLetterOfCredit.Amount to this value in tblPricing.[Calc Price]

How can I do this?  I am not sure if this is an "Insert into tblLetterOfCredit" code.

If I have missed something let me know.  

Thank you

I want to know how an expert would import excel data that does not have a true [ID].
I am importing an excel spreadsheet that I do not have control over.
If I did, I would add an [ID] but I dont know if that [ID] must stay fixed for the life of that record.  

I have something that is close to an [ID] in the excel file but its not unique in every case so I have to use [CompanyName] and another field [TradeNo] and these 2 are essentially my [ID] when I build reports, append data and display data on forms but doing this bothers me as I dont have one unique [ID].

I want to propose a meeting with the dept that controls this excel file and tell them that to import data correctly, I need a unique identifier for each record and the data set doesn't have one.  However, if I let them control this [ID] field then I don't know if simply dragging down the [ID] in excel is the approach to creating [ID]'s.  I have to import this excel data set almost daily and I don't know if an [ID] that was on say record 2 was for example [ID] = 10 and now for that same record is [ID] = 20 on the next import and then [ID] = 30 for the next import and so on and so forth.  Maybe it doesnt matter if the [ID] dynamically changes like that. The owners of the excel file constantly add and delete records and to populate the [ID] field, they would simply drag down [ID] from row 1 to row x.  Maybe there is no issue created if you drag down this [ID] field in excel because its not necessary to …
Experts, I have a report and I need to add a “My Group” to this report in the query developer.

If [DateSentToBank] is not null and [DateOfIssueSB] is null then “Pending”
If [DateOfIssueSB] is NOT null then “Issued”
So, if at anytime, [DateOfIssueSB] is not null then its "Issued" but if [DateOfIssueSB] is null and [DateSentToBank] is not null then "Pending"

The below is not correct but It would look something like this:
MyGroup: IIf(Nz([DateSentToBank],"") and notisnull([DateOfIssueSB]) "Issued","Pending"))
I dont think I can use notisnull like I am doing.

I might be going about it the wrong way, open to any new suggestions how to do this.  

Thank you
pass through query not working in access
inerting records from local table to sql server?

INSERT INTO t_sst_BrandMasterTest ( Brand_id, Supplier_Number, Supplier_Name, Brand, Preferred_Supplier )
SELECT BrandMaster_acc.Brand_id, BrandMaster_acc.Supplier_Number, BrandMaster_acc.Supplier_Name, BrandMaster_acc.Brand, BrandMaster_acc.Preferred_Supplier
FROM BrandMaster_acc

Open in new window

Error says table  BrandMaster_acc  does not exist ?

In Access I have created a customer database and an invoice for my customers.  When a new customer comes in I input their info in the customer database and then in the invoice I us a combo box to find the customer and populate the info in the invoice. The problem I am running into is that when I enter a new customer and then go to the invoice it cannot find the customer info..  I have to click on refresh, refresh all, before it will see the new customer even though I have created a saved button and click on "save".   Is there a way to get Access to save the customer record so that I can go directly to the invoice and pull up the customer info without going through so many steps?
Hi I am getting the error "The action or method requires a file name argument" in the following

DoCmd.TransferSpreadsheet acExport, , "q_TransferTransactions", , True

Is it not possible to just export the query so that it opens in Excel?

I have a report.  I would like to add a button in my form to save the report as a PDF to the location the user specifies.

Thanks much!
It's been a while since I used IDBE Ribbon Creator in an Access 2013 application.  I'm setting up a new application a designed the ribbon using IDBE.  At this point there isn't a lot of functionality built into the ribbon so there are only two tabs, 'FIle' and 'Action'.

As best as I remember I followed all of the same steps as in previous applications.and set up the ribbon to be the main ribbon in the application.  My ribbon shows but the development tabs (Home, Create, External Data, Database Tools) are also included.
Menu with Dev TabsThe arrows are my tabs.

In other applications that I used IDBE the development tabs don't show.
Correct RibbonI'm sure I forgot a step needed to supress/hide the Development tabs but can't remember how to make that happen.

Does anyone know?
I would like to use a function to determine which field of a table to use in a rowsource of a form. The following works if called in Load event of the form:

Me.cmbCustStatus.RowSource = "SELECT Code, Txt" & gUser.Lang & " FROM Codes WHERE ID=5 ORDER BY CODE"

However, I have not found a way to use this directly on the form properties. I tried

SELECT Code, Txt1 & gUserLang() FROM Codes WHERE ID=6 ORDER BY CODE;

but did not yield the desired result. Thank you so much for any help.
Hello Team

I have a userform which contains a tabcontrol with seven pages. On page 7 (6) there are nine checkboxes.

What I require is that, when the user selects to save the record, code is triggered that checks to identify if at least one checkbox as been selected. If one is identified then the save is allowed to proceed  If after all the checkboxes have been analysed and no checkboxes have been selected then the user will be alerted that no checkboxes have been selected and then exist it the save procedure.

Form Name:             frmRewnalAudit_Bus

TabControl Name:   TabCtlCats

Page Number           7 (6)  Page tab is named 'Doc Check'

Each check boxes follows the sequence, Check1, Check2, Check3 etc

Ideally, for example, what I am looking for is, code to loop through checkbox which contains the left string 'Check' and once a true value is identified, exit the loop procedure and execute the save code or after all checkboxes have been looped and no true value has been identified, exit the save routine.

Thank you in advance.


Microsoft Access





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.