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.

Hi.

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?
0
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.
0
Experts,

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.
EE_NotIn.accdb
0
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)
   db.TableDefs.Refresh

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
0
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!
Joy
0
Hi

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

Thanks
0
Experts,

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
0
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!
0
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.




Image

qrySumAgedCase1.xlsx
0
I'm using the following code and receive:

Image
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

0
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
0
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.
0
Experts

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
0
Experts,

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 …
0
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
0
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?
0
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?
0
Hello,

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!
Joy
0
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?
0
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.
0
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.

Dale
0
I must connect to an old Microsoft Access DB (.mdb) using python pyodbc.

Here's what i've installed on my computer:
  • Microsoft Access 2019, 64-bit
  • Microsoft Visual Studio Code
  • Python 3.7.4, 64-bit
  • I've done the pip install pyodbc

I've tried several connection strings, but none of them work.  I always get an error message
('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
.


Here's some code i've tried:
_ConnStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\TEMP\\MYFILE.MDB;Persist Security Info=False;'
_conn = pyodbc.connect(_ConnStr)

Open in new window


I've seen previous post about people using 32 bits of python vs access 64 bits, but it's not my case.  I'm using 64 bits for python and for access.

Thanks you
0
I have a query named RandSt_byState_qry. The query has the following fields:

StDescr_1           (this is a field name)
StateAmount1    (this is a formula)
StDescr_2           (this is a field name)
StateAmount2    (this is a formula)

Is there a way to get all the above as follows via a new query? I want all the contents of the first two items above in two columns and the last two items below the first two items.

Have:

StDescr_1   StateAmount1  StDescr_2   StateAmount2

Want:

StDescr_1   StateAmount1
StDescr_2   StateAmount2
0
Hi,

In ssrs I get diff from two dates.
How do I get also hours and minutes (now some results show 0 since diff is hours minutes only.

Thanks!
0
I have an Access table (tblAgedCaseGoal) with 10 data elements including 'Appeal Number', 'AID' and 'Appeal Assign Date'. How can I remove duplicate 'Appeal Number's where the "Appeal Assign Date' is the oldest date. The remaining 'Appeal Number' should be the latest Appeal Number based on the Assign Date.
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.