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.
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
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 .
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.
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.
I have an excel spreadsheet, that has a long list of names and unique numbers (of medical lab results)
I have another folder which has many text files (the actual lab reports), each of which has a file name that corresponds to one unique number in the excel spreadsheet.
How can I import the content of the text files into a single memo field (e.g. "LabResultContent") in the original datasheet?
im working on a project , and i need to add a sale and delivery option like a table connected to a member customer with product list and price and in the end put all together in a report. Like a bill.
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
Just got a new Windows 7 computer and installed Office 2013. I have a report that saves itself as a pdf in a temp folder and then attaches the pdf to an email and sends it out. I use a macro to run the report and everything works fine from there. I have a VBS file that runs the macro and clicking the vbs file makes everything work fine. the code is below.
When I tried to run it though the task scheduler it show as completing fine, but it doesn't. So I added a reference the vbs in a batch file (below) and output the logs to a temp file. After I ran the batch and opened the log I seen the message
I turned UAC all the way down. turn off the virus scanner, and have local and domain admin access. I'm at bit a lose here. I had this working before but can't seem to remember how I did it. This is just one example I have several other reports that were schedule to run overnight, but they basically all use the same code and processes.
When i click a button on the main form the vba code will execute and get the value from the combobox ie TE4700 and locate the macro with the same name ie TE4700 with reports in then it will run the macro and print off the reports.
is it possible to run a macro in access 2010 on close event of a word doc. i want to run an update query when i have sent some email. i want to first make sure i send the emails and the when i have finished close the word doc and then open query [QueryName].
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 ??
The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.
I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.
The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.
The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.
I need to display a message on a form after a record update has been done depending on whether a table field is ticked or not. If it is ticked I want one message if it is not ticked I want a different message, but here is the scenario.
I have a form and one of the options I have is to call up an address from a record in a table. I want it to display a message to advise whether there is a special requirement for delivery to the address, so in the table I have a checkbox field. If its checked, once the address has been pulled up I want it top advise 'special requirement' or 'no special requirement'.
I need to do this in a macro form, or expression form if possible as I already have an after update macro when that drop down is selected.
So on the dropdown list I have the following on the afterupdate function:
SearchForRecord Object Type : Object Name : Record: First Where Co0ndition - - "[ID] = "& Str(Nz([Screen].[ActiveControl],0))
That brings up the address from a record in my table onto this form, so what I want is if that record has got a tick the checkboxfield it prompts with a message and ok button.
enable me to run some code before a button is clicked I want to be able to click a button via VBA code.Basically I used the auto button wizard to make an add record button.To ensure a shed loads of if checks and queries on the entered form data run I wanted to make this button invisible, then make a new button running code not embedded macros to run my checking module then click.
I have a tabular form that lists all the records (address book). I'm trying to have a combo box filter the records by selecting a contact type (employee, supplier, customer). So far I have the contacts displaying in the combo box.I've tried to create a macro that begins "after update". Its an ApplyFilter macro.
Where Condition = [Contact Type]=[Contact Type Select]
[Contact Type Select] is the combo box.
When I do this, a popup box appears. When I type in the item I want, it works. I want this done automatically.
Is there anyway to customize the background of a message box that is brought up via a macro or VBA event? It would be kind of fun to have a stop sign image in the background of a "save and close form" prompt.
We have been calling a main form to manage yacht races using a hyperlink/macro from a selection form which lists all the yacht races ..The main form was too big to fit on some screens so we changed it to pop up mode to allow it to be resized and use scroll bars. this works well when the form is opened manually and all functions run just fine
the hyperlinik/macro for race selection had originally been set up to call the main form in dialog mode but we have had to change this as it disabled the resizing of the form and the scroll bars..The macro tool doesn't offer pop up mode so i selected window mode = normal and the main form opens with a resizable window and scroll bars but all the functions called from this module now fail (error message = use of null value or similar and all the functions go into debug mode - the server is down and i can't access the system to get the exact details)
If i change the macro in the selection form back to window mode = dialog the functions work but i lose the resizing of the main form and and the scroll bars.
1 table(1): record of people & contact details 1 table(2): list of events with check box's with the names of people from the other table 1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
I'd like to use a macro to display only those items which stay as a group in "Old Project Code" (black font) and filter out those which are singles in "Old Project Code" (red font) as shown below extracted out from a worksheet called "Project" in Excel.
1. The color wasn't there in original sheet. It is here only for demo purpose. 2. Data are changing dynamically, so need a macro in place to catch a snapshot at the time of macro is firing.
i orginally thought i should do this in excel but i think when the dataset becomes larger and i want to eventually get it on the web - i thought its access i need?
i have created a mini dataset as an exmaple of what im trying to explain..i have in first column the objects, and columns after this are the elements needed to make the object (i have just labelled the element titles a b c etc - there will be LOTS of elements once i get the dataset created). so if i had a red blue green cream elements - a search would bring back both lights and lamps that i am able to make.
object A B C D E F G H lights red blue green cream lamp red blue green cream garage red blue green cream yellow brown purple pink house orange white green blue red black garden orange white green blue red garden house orange white green blue red
is there a way of creating a search lookup to this kind of information? a form which you type the criteria in and it brings back what you can make? if i was one element away from an object could I use a wildcard function to say, if you had black you could make a house?? etc
Code: Private Sub cmdRunDataMacro_Click() DoCmd.SetParameter "prmMetalID", Me.cmbMetal DoCmd.RunDataMacro "Metal.GetMetalParameter" DoCmd.SetProperty txtTestMetal, acPropertyValue, [ReturnVars]![retPrice] End Sub
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.
