Proper Way To Look Up And Manipulate Files
Mar 2, 2014
I am trying to build a DB to fix movie folders and file names and I am struggling to properly find the file names.
Currently I am using "DirectoryName = Dir(folder, vbDirectory)" to find the files and loop through them correcting the names as I go.
Works perfect.
The issue I am having is how to properly then look up the file within the folder since Dir is already in use for the loop.
The straightforward question is how does one look-up a child file when the parent is known without using Dir().
Below is what is works to rename parent folders. I am sure it is hack to the trained eye but it gets the job done.
folder = "E:Videos"
DirectoryName = Dir(folder, vbDirectory)
Do Until DirectoryName = ""
If DirectoryName <> "." And DirectoryName <> ".." Then
If (GetAttr(folder & DirectoryName) And vbDirectory) = vbDirectory Then
[Code] .....
View Replies
ADVERTISEMENT
Apr 11, 2006
Please try help!
I want on my form some field to connect to field in other table. I would like, when I open form and insert txtName, verify if in field on other table precede that value, and if exist. If exist then find that recordset and open. But if that field don’t exist, open other form and fill that value.
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from vFizickoLice"
.AddNew
' !DeteJMBG = Me.cmbDeteJMBG
!DetePrezime = Me.txtDetePrezime
!DeteIme = Me.txtDeteIme
.Update
Me.cmbDeteJMBG = !DeteJMBG
End With
View 1 Replies
View Related
Apr 4, 2007
Is there a way that you can manipulate a pivot table, (example adding more item in a list) after the pivot table is created.
View 5 Replies
View Related
Mar 1, 2007
Would anyone know if there is a way to manipulate the current user value through code. I have a database and have all users signing on via the Admin user (basically not activating the password on the Admin user). I have a form that does request a password and username, which I use to allow access to various forms, etc. I know I could have done this through access security, but I needed to add some specific rules on the sign in that cannot be done through the standard access security sign-in. I am then able to store the username given at sign in and use this to allow or disallow access to various forms. I can also track when people sign in and sign out using this method.
Anyway, what I would like to do know is be able to set the system currrent user cuser variable to the user name they signed in with , and my reason for this has more to do with possible record locking conflicts if everyone effectively signs in as admin.
Is there a way to change the cuser variable through code ??
Thanks
Jackson
View 1 Replies
View Related
Feb 25, 2015
I've nearly cracked where I want to be. I have essentially had to do the core of the back-end in SQL in order to organize the data in a way that actually makes sense to ACCESS.
Following this I now have a table with the following fields
Stock No
SK Code
Free Stock Qty
Stk Qty Pd
Actual Min
Actual Max
Forecast EoM
Forecast +1Month
Forecast +2Month
Essentially this is a stock forecast sheet. It takes into consideration incoming stock and estimated usage to provide an output as a "Forecast" stock level at the end of the forthcoming months.
The next phase of developing this is to allow users to manually change the front end by Group and Part Number with Quantity entry so that it auto updates the Estimated EoM, +1Month or +2Month figures depending on which month figure they are looking at. In addition, this information would need to be retained and ideally stored as individual tables or other .
View 2 Replies
View Related
Jun 15, 2013
I am trying to manipulate a find duplicates query using the following criteria:
Fstnm L2, Lstnm L5, Add1, Zip
This is what I have done so far:
SELECT [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM],
[Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP],
[Duplicate Identification Dataset].[ID], [Duplicate Identification Dataset].[MIDNM],
[Duplicate Identification Dataset].[SPFSTNM], [Duplicate Identification Dataset].[SPMIDNM],
[Code] .....
View 1 Replies
View Related
Jul 13, 2014
Access file attachment in the form below if you want to add two columns (like other columns with the capability to filter).
Link file attachment : [URL] ....
View 14 Replies
View Related
May 5, 2015
I have some tables need to be imported to Access, which is not well structured. For example:
report
4/05/2014
from SQL server
Name ID Add.....
aaa 111
bbb 222
To be more specific,I have 4 csv files, that need to be imported into Access.
1. I'm building 4 linked tables so users can update the table as needed. Then run queries based on these tables to produce report. But maybe there's a better way? the file name and location might be dymatic, so anything like 'getopenfile' would be easier instead of linked tables?
2. These data need to be clean up first to become a 'database table'. (Delete first 2 rows, delete some columns, remove duplicates etc.) I know how to do them in VBA Excel, but never used Macro in Access before.
View 1 Replies
View Related
Oct 14, 2013
The key field is the specimen number and specimen type. Currently for every antibiotic there is a row with its result. My output is 3 complete worksheets so a lot of data. I need to create a database with one row per specimen number/type to include whether it is S/R/or I for each type of antibiotic. The example probably makes more sense. but I'm assuming that 65000 rows * 3 sheet can then be reduced to around 18,000 rows.
View 4 Replies
View Related
Aug 16, 2011
manipulate the data by changing one of the columns (Customer 1) to a Look Up Field. This has created two challenges for myself:
1) The data is gone ... oops. I can solve this one.
2) More serious, that column (Customer 1) will only display primary key numbers of the data. Before the corruption, I had a combo box on a form. This combo box took its values from a Table called CustomerTable, the value in this combo box was stored in the QuoteListTable. Before it showed names, now only primary key.
I have attempted to:
-change the look-up back to a regular field (got rid of the look-up)
-exported the table to a query, turned it back into a table, no change.
View 1 Replies
View Related
Jul 2, 2013
I have created a report and, in the unit price field, I have set the decimal place to 5 in the report properties. Sometimes we have pricing for items up to 5 decimals long, such as $10.02985. It is rare though, so I'm to see if there is a way to have it drop the zeros down to 2 decimals when more is not needed. I'm thinking maybe a VBA event might work, but not sure.
View 12 Replies
View Related
Nov 11, 2013
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
View 7 Replies
View Related
Jun 2, 2005
And I thought this would be easy. I am trying to make a db of storytelling kits for my workplace. I thought it would be easy. 3 tables. Link. Make a form with the kit details and 2 subforms where I'd enter all the books for each kit and all the activities associated with the kit. Each page would show only the books/activites that the kit contained.
Pfft.
None of that worked so I'm going to be a real dummy and ask what to do with my relationships.
So far I have
Kit
-ID
-Name
-Genre (lookup table, non changeable values)
-Location
-BookID
-ActivityID
Book
-ID
-Title
-Author
-Format (lookup table to another table)
Activity
-ID
-Name
-Description
-Materials
-Age Group
Format
-Description
Format is linked to book (cause it was just so I could have an easily updated lookup). Activity and Book are both linked to Kit. Each kit has many activites and many books (fine) and I'm tempted to just leave it as each book only has one kit (even though that may not be the case in the future). How do I set it up so that it works? I enforced referential integrity but that screwed up my form by telling me that I couldn't enter data into the kit area until I'd entered it into the book area which was useless as we enter the kit details and then find things to add to it.
Any ideas...just in general.
Sorry to be a pain yet again.
View 2 Replies
View Related
Jan 25, 2006
I recently created a database and installed buttons to properly close the database to avoid coruption of files etc. However I could not find a way to eliminate the X button in the upper right corner of access. Today my dbase crashed, I presume because someone did not use the buttons I installed, and just closed using the X in the upper right corner of access. The database is on a network, and was not password protected at the time. It will be when it is restored, however I would still like to know if there is a way to eliminate the x button in the upper right hand corner so that users will have to use the buttons created to properly close the database.
View 4 Replies
View Related
Apr 12, 2007
This is kind of a weird question. I downloaded and imported a table with cities and zip codes in it. All the city names are in upper case. I want to use the city names in that table as the row source for my City text box.
But in my database, I store city names in Proper Case, with just the first letter capitalized. So when I use the table as the row source, it pulls in the city name all in upper case
Is there a way, either within Access or not, to change the city names in that table to Proper Case?
View 4 Replies
View Related
Jan 3, 2008
Is there a way to correct an address field to Proper using a query? I have existing data with various formats. I am not able to correct it, but would like to create a new table using the data but with proper format. I do not write code.
View 3 Replies
View Related
Jan 21, 2008
Hi Folks,
Does anyone know how I can use the Proper Function in an append query.
I have a field whereby all the data is in UPPER case, but I only want the first character of each word to be upper case.
I have looked up the Proper function, which should do the job, but when I apply it in the specified field I get an "Undefined function "Proper" in expresssion message
my expresssion is as follows: salutation: Proper([strSalutation])
Any assistance would be greatly appreciated.
John
View 6 Replies
View Related
Feb 5, 2008
Hi,
If I have 2 date fields (start_date and end_date)
And I want o create a query, that captures everything either on or betwen those 2 dates.
Would my query be
On the start date field.
>=[Forms]![frm_PRC_Yes_search]![txt_date_start]
Then on the end Date
<=[Forms]![frm_PRC_Yes_search]![txt_date_end]
I think it is, but just want to make sure that I'm not leaving data out.
View 3 Replies
View Related
Mar 5, 2008
Hi,
I hoping someone might know what expression I need to create to convert text in a field in an append query to proper case.
The reason for this is that I am receiving data in all uppercase and really need it to be in proper case, that is only the first character of each word in the field is capitalised.
A field name for instance is strProductDescription and I want the data to change from "FRIDGE FREEZER" to "Fridge Freezer" on appending the data to another table, hence a proper case function or expresssion.
Any assistance would be most appreciated.
John
View 10 Replies
View Related
Dec 12, 2004
(I use the sample database "Northwind" for my question)
I wish to do as follows: I added a field to the "customers" table, and name it "customer status". This field should be updated by update query as follows:
I wish that for each customer that his total purchases wil be calculated through the "order details" table. If I multiply the "unitprice" in "quantity". I know how to make totals query, so I can see the total amount of orders per customers.
I wish that the "status" field will be update as follows: if the total amount per customer is higher than 100,000 the status will be update to "Gold Customer", if it is between 50,000 and 100,000 it will be updated to "Silver Customer" and the rest will be update to "Standard Customer"
Please advise how can I do that.
Thanks.
View 2 Replies
View Related
May 1, 2012
I have a table which contains information about personnel. There are several fields which I want to have consistent values inputted. For example, somebody might populate the "State" field with: California, CA, Ca, or C.A. To avoid this, I created a seperate lookup table with a list of all states, fully spelled out, and the digraph abreviation associated with each state. The digraph is the primary key for this new table. In all, I have 12 such lookup tables.
Having all these extra tables, while nice for clarity sake, seems excessive. In most cases, the lookup tables really only require one column, though I've always used a minimum of two (one is an acronym set as the primary key, and one is a fully spelled out description). I'm finding the primary key is often not useful to somebody reading the data; the full description is much better.Could I have one catch all lookup table which combines all of the standardized fields which I want to use, and have no primary key for said table?
View 2 Replies
View Related
Oct 13, 2005
Is there a proper time? Or is it just anytime after you create the tables.
Just wondering?
I’m about to deliver part of a project today so they can start entering data. Much of the development is yet to come. Should this be split?
View 6 Replies
View Related
Nov 26, 2007
I have a field (date field) that when I try to imput data will tell me that that what is being imputted is not in the correct formate or to large for the field settings. It is in the right formate etc. Is the field size applicable to just that field or overall everything entered in that field in the whole database? It's a decient size data base and I'm wondering if everything in that field is over the size, but then how can that be?
Help!
View 4 Replies
View Related
Apr 30, 2008
New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.
All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.
It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:
A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:
Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix
...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.
Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix
Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price
In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.
Now, will the way I did it hold up? Is there a better, more correct way to do it?
Thank you for the time & help!
View 4 Replies
View Related
Oct 12, 2006
Hello all:
I have a database with the following fields: Last_name, First_name and Mid_name.
These fields are concatenated into a field called Full_name using this method: =[First_name]&" "&[Mid_name]&" "&[Last_name].
I wish to have the Full_name field convert the text in it to proper case.
Any ideas on how to go about it?
Thanking in advance,
Dion
View 3 Replies
View Related
Mar 26, 2013
I have updated my copy of a database which included updates to a number of forms. I now want to import those changes into the proper database, so I used the Import function. I didnt get any errors when I imported everything, but the forms do not work as they did in my copy of the database.
One of the forms that dont work comprises of a number of drop down boxes, I have updated these so that the user has a filtered selection, to do this I put an SQL function in the Row Source property. This SQL function has been copied over correctly in the imported form, in fact when I go into the form in design view to check this fact then revert it back to normal view (after having not changed anything) it then works and all the other drop down boxes that didnt work on that form now work! How can viewing a form in design view and not changing anything make it now work?
So I could go into each form in the design view, check the Row Source property, then revert back to normal view and it would solve the problem of my forms not working, but, this database that I am importing the forms into is the back end of a split database and the first thing that the front end of the database does is import the changed objects into its database but the forms dont work.I have made changes in this way before and not had any problems before.
View 2 Replies
View Related
