This is for anyone who has made a form with a lot of check boxes and wants to make a report out of them thats decent.
Hopefully this simple example file is enough to assist people.
Keywords:
Checkbox Checkboxes report check boxes box
What would be the best way to store data in a database that could change but without effecting previous linked records?
Let me try & explain a little better.
I have a table that lists materials with the cost price & sell price of each item
When I use that info to create a record I will only store the id no that the material is related to then link the cost fields to display the prices without storing the data again.
The problem I face is that in the future if any of the costs are changed in the main table then all linked records will also show the new amounts.
what's the best way around this?
Do I simply store the actual costs in the records when they are created allowing only the current costs to be used ?
I want the program to input an entry for every item with a value ranging from 1-300, according to sales data, according to the associated style.
I have to sort large amounts of Data and format them into a way I can deal with it.
I get two inventory lists twice a week from 16 stores.
I sort this data in two ways.
I do this by comparing an inventory list to a list of 300 of the best selling styles.
Currently, I input one list of inventory data in excel and then organize the items manually and respectively to sales data list in columns. Top 25 styles, 50, 100, 200, 300.
When I am finished, I have a certain amount of items in the top 25 styles, a certain amount in top 50, and so on. I color code the columns and write down the quantities and percentage of each category in the list..
if this is still confusing:
I have two tables.
one has the style numbers of which several items are in the category of a style number.
and one is the list I want to sort the previous list by. It has 300 styles that sold the best. they are rated from 1 to 300.
I want to organize all my items in each list by the 300 style list.
I have to do this twice a week, for 16 stores, for two separate reports.
ongoing basis.
is there a way of having the program do the numbers for me?
I am entering the data in excel physically . Is it possible to do this another way?
I am trying to sort the sales data sheet INTO the columns I have set up AND according to the styles that each piece of data has in common.
Hi everyone,
What is the best solution to deal with different currencies in an Access Application? After creating a table that includes currencies codes I want to use them in the form to present and calculate prices.
After choosing the currency from a combobox, the price field must be presented in numeric values with the chosen currency code.
Thanks in advance,
Best Regards
I have a database that has the basic tables of products, accounts and orders.
I need to figure out how to deal with returns.
Do I create a separate table and link that to the orders table for returns?
I haven't made such a large database file so I was curious if anyone had any information on how to lead me in the right direction. Or if there were any examples. (I've looked around but haven't completely figured out the best way to handle this.)
Thanks!
I have a database that keeps track of training hours for each employee. The Training Length is formated as Short Time. I just figured out that short time can only go up to 23:59:59. Some of my trainings will be over that. Does anyone know a way to get around other than splitting up my hours and Mins in the table?
View 1 Replies View RelatedI am using the following to extract date information from a text field as part of a query.
Narrr_Date:CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))
Due to users not putting the correct information in I will get the #Error in the results of the query.
I need to do a DateDiff on the results so I need to get rid of any values that come up with #Error.
Since I cannot ensure the date extracted by the query is correct, how do I deal with the error?
Setup
i have a form (frmCustomers) with a subform (sfrmJobs). As you might have guessed, it keeps track of customers and jobs. One customer may have many jobs. within sfrmJobs are about 300 or so controls for about 300 or so fields (mostly yes/no boxes) and at the moment contains two subforms of its own. customers have an autonumber CustomerID and jobs have a user-entered numeric JobID.
Goal
it has been brought to my attention that some jobs may just not work out and must be deemed as "cancelled." however, my client wishes to retain these records rather than delete them. in order to maintain the integrity of the reports, i would like to remove these records from the non-cancelled customer database and place them elsewhere, but still retain the ability to review them if need be (but NOT to modify them).
Problem
the job can be cancelled at any point down the line, meaning that a job could have nearly no info or nearly all the info, and everything in between. each job record has a ton of fields. i don't know how to cut and paste an entire record (except for doing it field by field, which could take forever in this instance) into a new table. in this case, it would be several records, since i have a list of materials pertinent to each job as well as a list of services necessary for each job - all of which would have to be removed and placed elsewhere.
Cry for Help
what is the best way to go about this? some ideas i had were creating a parallel table for each table used in recording all of the job information and just manually (well, via VBA and recordsets) copying, pasting, then deleting. another is creating a checkbox that when clicked disables every control for the cancelled job (but oy vey that's a lot of controls to code the disabling for) and then have it reflected in my reports that these are only from the NON-cancelled jobs. does anyone else have an idea?
help?
thanks in advance
-Jason
Hello all,
Ok, let's see if i can explain this so that anyone trying help can understand. I have a form that's unbound (Music) with a subform (Arist Info)that is bound. The subform also contains two other Subforms (bound). The subform (Artist Info) contains a few Macros set up to do
some general searches with some Option groups and also one cmb box with vb code that access wizard wrote up. They all work great, with no problems at all when I open up the subform all by itself. It's when Main form is open where i run into some glitches. In then option groups all tgl & radio buttons work with exception to the two named ALL. I get a ballon message stating There is no field named
'GenreGroup or AlphaGroup' in the current record. Genre and AlphaGroup are the names of the option groups. The cmb box dosen't even work at all. There ars some other problems also but i'll ask for help later if i can reslove this. LOL I've attached a bare bone sample db to look at. I thought I was doing great learning new things every day, till I ran into this one.
Thanks for your help
Rich M.
I have a database that was populated using many excel sheets and one of the fields [partDescription] uses the # character over and over to represent "lbs". I realize that this is a wild card for digits in VB. Is there a quick fix for finding and replacing this character?
View 2 Replies View RelatedHello...
I created a button that opens a link to a URL and copies information from a record to the clipboard to paste on this webpage.
The users are not using it correctly (they need to close the page each time they need to do the next record).
what i want to do is to close ALL IExplore windows via access if there are any open then open the URL again. Sounds pointless but we have to close it because of security.
Thanks.
I have an imported file coming from another department and the one of the field names I need to use has spaces. I have tried " " and [] but they don't work. What is the proper syntax for this?
origfield = rst.Fields("FVH PLAN")
Thanks.
I have a form that I designed that contains two subforms.The first subform is an entry subform so you can post the current client visits.The second subform I want to just display all prior client visits and make updates or deletions if needed.
It does not appear that I can make the first subform entry= yes additions allowed=YES in the properties.The second subform I want entry=No and addition=no..When I pull up the properties sheet for each of the subforms these options do not appear. They appear only on the main FORM, FORM property sheet. I thought I could create two new forms with the properties I desire and then copy them into the main form.
I have a database with student information that contains tables about their dissertation and graduation information. There is a field "academic year" noting their graduation year. I have a form for data entry that my data entry person likes to use in datasheet view. The form is based on a query that contains only current academic year records. When a new academic year arrives, I plan to create a new query for the form to feed from. i.e., "hiding" past academic year records on the form in datasheet view.
View 1 Replies View RelatedHi all,
I am in desperate need of some advice. Today I set to work building some math functions in my queries to calculate delays/turnaround times for work, etc.
I have found that where there are null values in my table - the calculation seems to break down.
Let me explain...
In it's simplest form (I have removed some fields and will deal with DATE only, not TIME which is a seperate field) I have the following -
Date_Received
Date_Started
Date_Pended
Date_Recommenced
Date_Completed
I have the following expressions in my query -
Turnaround: Date_Completed - Date_Received
TimePended: Date_Recommenced - Date_Pended
TimeInactive: Date_Started - Date_Received
Workrate: Turnaround - (TimePended + TimeInactive)
The query calculation will only work when all fields contain a value.
For instance, sometimes the work may be started, then completed. Skipping the 'Pended' and 'Recommenced' steps.
Can anyone tell me what I need to include in my maths for Access to treat a null value in the table as zero? Or any other approach.
Please help!!!!!
Thanks
Rob :eek:
I have a large amount of field data that was taken by a data logger. Specifically, The datalogger has five temperature probes embedded in soil, and takes a measurement automatically once every hour for each probe. So the table has a Time field, and then a Temp1, Temp2,... Temp5 field. I need to be able to average the temperature fields. This within itself is easy enough, I just tell the query to find (Temp1+Temp2+...+Temp5)/5. However, unfortunately, because it is field data is is very common for one (or more) of the ports to be malfunctioning at any one measurement time for a number of reasons.
If, for instance, Port one does not have a value, then the formula I entered will not work, as it can't add a null value to numbers, and otherwise It would be dividing by five whereas there were only four values to average.
Hi,I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.It may or may not be commercial software.If anyone knows of such an available solution, please let me know.(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)thx
View 5 Replies View RelatedHi,
I haven't worked with Access for a while, now i'm working on a project and just can't handel with a calculation. I have somewhere the solution for my problem, I had use it other times, but now i just don't know where to find that sample database.
I have 2 tables: tblProjects and tblCosts
tblProjects
ProjectID
ProjectName
ProjectValue
...............
tblCosts
ProjectID
CostName
CostValue
................
What I want is to calculate the Benefit=ProjectValue-CostValue.
I know it is possible, in other cases I have used some union queries, sum calculation and I had my results very simply. But now, as I said before, can't find that piece of SQL. :(
Any solution is appreciated.
Thank you
Hi,
I'm looking for a bug/issue tracking solution done entirely in MS Access. Does such a thing exist?
My requirements are that it must need only Access, and be accessible in a shared environment solely by opening a .mdb file from a shared folder. It must support various issue lifecycle related things, and the stuff those tracking systems do in general.
It may or may not be commercial software.
If anyone knows of such an available solution, please let me know.
(And yes, I've searched on Google, and haven't found anything worthwile, so that's why I'm asking here now.)
thx
I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.
New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))
The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.
I need to try and create a simple form that a user enters data into and then hits a print button and the text they entered is printed in a particular way.
i.e. they type in someones name, job and company into 3 fields and then hit a print button and this then prints :
PERSONS NAME
JOB TITLE
COMPANY
We also need the print to be formatted a particular way but that is another issue
This is for a small exhibition we are trying to run and we need something to print visitor badges with
Has anyone got any ideas that can really help as we have been let down by someone who was going to do this for us
Hi the specialists.
I do not want security on any of the files in my computer.
I'm placing this in "general" because I'd like a direct and practical answer, please. And if there is none, please say so. Computers are immensely complicated and I am just a little bit tired of people superciliously refering the uninitiated to "Microsoft's FAQ's" That source is to most normal humans as obscure as the software programming itself.
I have also searched this source and Google for hours, without getting an intelligible answer.
This is my problem:
I am the only user and administrator on my computer.
I have a back-end file which is easily accessed through the front-end.
.... but
I cannot open the back-end to access the tables directly.
I get the following error message:
You do not have the necessary permissions to use the 'C:Documents and SettingsAll UsersDocumentsAccessThingsWORKLOG_be.mdb' object. Have your system administrator or the person who created this object establish the appropriate permissions for you.
Of course the person who created this back-end is me, but I have no clue what I did, because it is at least two years since I created it.
Could someone please help?
I have tried to use the "shift", click method. It does nothing - just gives me the same message.
Pierre.
Link to the original thread (http://www.access-programmers.co.uk/forums/showthread.php?t=89557)
I have realized what I was doing wrong and thought that I would post the solution in case anyone else does the same trying to implement security. First off thanks Pat Hartman for the input. You were right on there needing to be a cutoff and now one is added that ensures they can't edit punches after payroll has started.
I had the right idea with the many to many relationship to get a list of buildings. What I was doing wrong was joining the resulting table to the shifts table. Instead the correct way (well, it works anyways) is include WHERE Building IN (SELECT ....) in the sql where the select statement gets the list of buildings numbers that I have access to.
Now the list is limited to the buildings that they have access to and when you delete only the shift table is affected because none of the other tables are joined.
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Thanks in advance!
Gr Billy
Good afternoon all,
I have a A97 Db. On one of my forms (see attached screen pic) I have a field "Payment type" for either Cheque, card of Account. There is no code or functions behind it, it just stores a value.
Trouble is my users keep forgetting to fill it in!
My first (and easiest) solution would be to make "Payment type" a required field. However. The field will only be filled out under certian conditions.
That is if the "Status" field value = "X" (drop down box holding two values "X" and "Y").
What would the code be? I presume it would be in the Form "after update" field? Would read somthing like:
if [Status] = "X"
and
if [PaymentType] = "Null"
then
mssg box "Please enter payment type"
I have a vba book on order to start learning, but as you have probably guessed, I have not received it yet! :)
Any pointers or info would be much appriciated. Many thanks :)
I need a little bit of advice on this one.
I have 2 tables that are used for different things, one table, denial data, is used for tracking all requests. It is updated with the form, Daily PAs. On the form are 2 buttons, each running a macro. One button exports the data to Excel by running a query to specify the date range. The second button is my problem. It also has a macro, with an append query, to append only requests that have been denied to my second table, Denials, to be later updated with additional information. The append isn't working because I am getting three different errors, a "type conversion failure," "key violations", "lock violations" and "validation rule violations." Now, I know I can begin working out these violations and get it working, but I'm sure that involves a lot of time and coding. I would really appreciate any other suggestions to accoplish the same tasks. I need to keep the approved requests seperate from the denied requests for auditing purposes. Thank you for your help.