hello all, I am very new to access (my background is in VBA Excel, but I am not using VBA for access yet).
I was wondering if some of the more advanced people can give me pointers on how to accomplish (or work my way) to make the kind of form I am trying to do.
What I am trying to do is the following:
I have a vending company with couple of products such as espresso, latte, black coffee, etc.. (all of the products are sold at the same price for each customer, so I might sell the products for company A for .75, but for company B for .55 etc) and I am selling these products to many clients company A, B, C, D, etc. As A start I created a table with my products, then I created a table for my clients (this one includes name, address, contact person, and machine number ).
I would like to make a readout input form where it would get the customer name, machine number, each of the product's total for the readout period (this is usually every week), and the date of the readout. Now I would like the form to add a record in a table for the client machine number that was selected and maybe also in another table for the overall machine ( I am not sure how queries work, and weather I would need that or I can use queries for it).
My ultimate goal is to be able to do the following with the data:
- check (and perhaps graph) the overall statistics for a each of the products for all clients
- do the same for a certain client
- do the same for a certain machine at a client
- check the overall sales of all products to all clients for each month of the year
- do the same for a particular client or for an individual machine
- do the same but for each year
I have done all of this in a VBA Excel based program but I was hoping to change the program to be access based instead of excel based, since this would make the data more centralized and easier to maintain, backup, check, and update.
So if anyone can post any hints or suggestion about how I should build my tables, the usability of quarries, and especially the construction of the forms I would very much thank you for that, since these are my first days getting to know access and I am absolutely confused with it, all the language you are supposed to put into text boxes, combo boxes etc....
Also if anyone knows of any good links to some online tutorials would be great, since most for the tutorials I found on a search was for constructing a for or a query using the wizard, but with no real discussion of how to bound the data to certain fields etc.
Usually I only have 1 link to a file, but sometimes some people want to add more than 1 file. How would my table structure and code work if i wanted to add a button to my form which says "add another file"
Hello dear experts! Will this table structure work? - Please help! I need to be able to see if certain members have paid subscription, see if stakeholders have paid for their room bookings, etc. Will it work, does it look ok to you?
Im trying to brush up my normalisation skills and logic.
I have a DB: This DB was sent to me via eMail (dropbox). It consist in a BE and 3 FEs. In each of the FEs I have a started form with some code activated by the Open event.
The issue: Access do nothing when I open the FEs. Note please that the DB is designed by me and it work very well on the user"s computers.
I use A2007 on Win7
The DB is in a trusted folder. Compact and Repair is not useful.
I have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this: (variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this: If (variable what I need to define) = 3 then msgbox "ok" end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
I have manipulated the duplicate record code found online to suit my purposes. It works for all the other subforms, however this subform has too many fields so the code goes onto the next line. How do I solve this? Online it says using "& _ " but I can't get it to work. There's basically too many fields to fit on one line so I need to continue the code onto the next line
Code with end of line problems highlighted in red:
If Me.[subformEmissionsControlTarget].Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO tblEmissionsControlTarget ( System_ID_No, EmissionsStandard, OtherEmissionsStandard, IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke,
I have a comment box that was just for text. I have been asked to adapt this to allow a web URL to be typed in which would then become a clickable hyperlink (like typing in a Word document). Is this possible?
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are: pkfDriverIndex strLastName strFirstName strInitial strAddress1 strAddress2 strCity strState strZIP datBirthDate
The fields in tblLICENSE are: pkfUpdateIndex fkfDriverIndex datDateUpdated strState strLicNumber datExpirationDate ynViolations ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
i understand the necessity of foreign keys to link tables and create dynamic data structures..
believe it or not i have a degree in this subject, but am finding one little issue quite confusing...i shall illustrate this with an example...
THE RIGHT WAY TO DO SOMETHING - 2 tables.. table1(DRIVERS) table2 (JOBS)
DRIVERS driverId name phone
JOBS jobId date driverId
each job is carried out by one driver, and drivers can do many jobs throughout their lives...obviously. i learnt that in good practice, the above two fields should be used to link these 2 kinds of tables together..
to my knowledge.. to link the two tables like this in access.. i would view the 'Relationships' and drag one field to the next.. so the little line joins them and everyone is happy..
HOWEVER, i have created this database and have used this method...imagine my user adding a job to the database.. all i want them to do is choose the drivers name from a dropdown menu for each job. to acheive this i have used the 'lookup wizard'... this successfully associates the chosen driver to that job and when you query the database, the results are valid. this link i have created has nothing to do with the Ids as illustrated in the above example. what i need to know is if i link the two tables in the 'proper' way using the Ids.. how can i allow my user to choose the drivers name when entering a job.. rather then choosing the drivers Id number on the form because that would be confusing... i hope this post wasnt confusing.. am i mad...someone please explain to me???
I have a question about the fast food project for the restaurant, how to make the structure of the table ! also they want to working by the touch screen , if the database i created by Access and SQL it is worked on it ?
We are currently using Access 2007 for all our database needs but so many different people have had access to changes things that now they are so convoluted that my boss is wanting me to build a brand new database... Is there a way to take all the tables in one database and migrate them over to the new database that I am building?
I need to daily import files that start with same name, but the remaining part will be Bill of lading ID (e.g. TEST_BOL 1234.csv). The next day will be TEST_BOL 1235.csv) and so on. I need for Access to import the file based on the "TEST_BOL" into my database and then delete the file from the folder. I don't know how to initialize Access to look for the prefix and import in.
I have recently asked about being able to use my table system online. i have just thought. what about creating a front end that works online. still have the front end on the desktop and the table system on the desktop but have both of them connecting to the one table system.
I'm writing some "Help" for an Access database that I've created, but lots of others will be using.
All I can think of is to type it all into a form which is then displayed when the user clicks a button I'll create on the menu. As it's going to be a long form, I want to put a "Contents" section at the top, with hyperlinks to relevant sections further down.
Is there a way to make one label (cos that's all the text is) hyperlink to another label in the same form? Or should I be going about this in a completely different way?
I've read a lot of the hyperlinks Q&As in the forums, but haven't found the answer to this yet. Thnaks in advance for any help.
I am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments.
The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I am too the point of creating 4 separate databases one for each department.
I have a form that takes a long time to open – around a minute and sometimes longer. The form is based on a query, and this query on its own opens almost right away. Why would a form take so long to load up? What factors can increase a form’s load time?
Is is possible to create a form that is 4 pages in length? After info is entered in first two pages I would like to print out all 4 pages to pass on to another person.
I'm having an issue where when I attempt to export data from an Access database to an excel spreadsheet using VBA it truncates any field longer than 255 characters to the 255 limit. I'm using
to export it (obviously with the table name and filepath filled in) but for some reason I cannot get it to export the entirety of the field to excel. I've been doing some digging on various forums around the internet and it seems as though it may be possible to split it into various excel cells then use automation to concatenate the cells. But considering this field I am trying to get not truncated can be up to 40,000 characters theoretically, it doesn't make sense to do it that way.Do you need more information from me? I'm somewhat new to both Access and VBA.
I am wondering why access does not follow its own code all of the time?
For example I have passworded forms following the tutorial on the Microsoft site. It mostly works when the participants fills in the wrong password we will get an error message box and when they cancel the password dialogue box it wont let the person enter the form - but not all the time. Sometimes if you press cancel it still opens the form anyway (without requiring the correct password).
This is similar with the module that I put in to prevent people from using the mousewheel to leave their record etc. Most times it works but sometimes it just isnt called on form load.
There is nothing wrong with any of these codes and when they work they work well but I cant seem to rely on access to do what is supposed to?
I use below simple command line to start a macro in my Access database. Usually all works well. I start my bat file, command is executed and macro does its job.
However it always fails if I have a session of Access already opened even if it is a completely different database. If I use below then it opens another sessions of Access but does not execute the macro. I need to go into the second session and run macro manually. It works fine then.
I currently have a "send object" macro which runs when a button is clicked on a report, it makes the report a PDF file and automatically places in my office outlook also places a subject and message into the Email.
what I wonered is there is a limited amount I can put into the body of the email in the field so I wondered if it is possible to use a Email template that is placed into the body and then the attachment is also added? I have tried to use the template file but it doesnt seem to work.
I have a table (Imported from Excel) with In and Out records.
Here are the problems I have:Each record has either In or Out data. I have an identifier to know which ones are In and which ones are Out
It might have some misses, either no In or no Out record for the specific work time. A worker can get in at the evening and get out at the morning of the next day. Might have more then one entrance/exit on the same date.
I want to have the record sorted so I'll have an In and Out on the same row (record) including holes for the missing ones.
I want to hide the Navigation Pane as well as the full menus and "special keys". I do that by going to the options tab and removing the appropriate check marks.However, it does not seem to work! No matter how many times I do it, if I hold the shift key, the navigation pane is still there.
