I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.
For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:
Part # part code Equip. Temp height etc
AB1234 A 15-1 100 5.3 ...
AB1234 B 15-1 200 6.3 ...
AB1234 C 15-1 250 7.3 ...
They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.
I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description. Table 2 has invoice number company details, address etc. Table 3 has product code and product description qty and invoice number.. Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard. This means the wrong code can be put with wrong description. What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber) Date (Short Date) Combo26 (Text) (5 Regions:- North, South, East, West, Central) Bookings (Number) Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then 'Define Message Box MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR" Else End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).
What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.
I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?
Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.
I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.
Tables: Input tables:
tblTV tblNPP tblRAD tblOOH tblONL
Ref Tables:
tblSEGMENT tblMEDIATYPE tblDMA tblDAYPART
For instance, fields for tblTV
TVID Station DMA Daypart Program Length DateStart DateEnd TRP NumberSpots Cost MediaType Cons/Bus CreativeDescript CreativeOffer Segment
So, my questions:
1. Is it acceptable to have five input tables, rather than one?
2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?
3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?
3. Relationships will be critical in getting this set up correctly. Any suggestions?
Any kind of direction, suggestions will be very much appreciated.
Essentially I am trying to build a macro or even a VBA script which would open an initial form with various data entry options, and then depending on the options chosen it would open specific forms sequentially with some preset data values.
I can go into more detail if needed; however, the problem is at my work (lab in an industrial plant) my coworkers and I waste a lot of time inputting data as our forms just include every field (which only around 10% of fields are utilized for each entry) because certain circumstances will dictate what fields need to be filled in, so for each entry most fields are left blank. These circumstances are entirely predictable as to what fields I will need for each entry so I thought I would easily be able to figure this out on my own but I have been unable to do so.
I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?
For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.
So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).
I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).
I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.
Is this possible and how do I go about setting it up?
I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.
Apologies, but I am still getting to grips with access to be honest.
I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.
In particular, I want to be able to enter into a form:
- a date; and - a class of asset
And have the relevant rate of depreciation for that asset class and that year appear in the form.
Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)
I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.
I have experimented with having two joins between tables, but that did not seem ideal.
Perhaps a query of a query?
Perhaps a macro?
(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.
I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :
Private Sub Command30_Click() On Error GoTo Err_delete_Click Dim stDocName As String Dim intResponse As Integer intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team") Select Case intResponse Case vbYes CurrentDb.Execute _
My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:
Code: Private Sub btnReport_Click() Dim strDocName, strWHERECondition As String strWHERECondition = "" If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then
[Code].....
When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.
I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.
EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.
I am currently using the column history to log the history of inputs into a memo field.
But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.
I no that I cant use the columnhistory command with a combo box.
When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.
I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".I can do this in a QRY as follows:
SELECT Projects.[Project Number], Lessons.[Actions Resolved] FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));
But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.
One of my forms has a load of data displayed in it from a query and I would like to allow the user to sort this data by means of some buttons next to each column heading. For instance clicking 'up' next to first name / last name etc will sort ascending on first name, 'down' will sort descending etc. I tried setting up different queries for each sort and changing the record source of the form in the on click event but then the controls don't seem to know where to populate from!!
I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.
The info on this forum I have tried the following Expression:
=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "
But my problem is that it tells me:
''The expression you entered contains invalid syntax You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''
Can anyone let me know what error I've made and how to fix it? It would be much appreciated. Thanks
I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.
The info on this forum I have tried the following Expression:
=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "
But my problem is that it tells me:
''The expression you entered contains invalid syntax You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''
Can anyone let me know what error I've made and how to fix it? It would be much appreciated. Thanks
Please Exuse the question I am about to ask. I am a bit rusty with Access and cant remember how to do the following. I have tried and tried to work it out and tried various help searches but cant get the answer I'm looking for.
What I need to do is create a data entry form, very simple! However I want the user to be prompted to enter a project name and number in order to find the project that i want the new data to be entered under. Then it will need to create a new record in that project ready for new data to be entered from the data entry form.
I am trying to build a query that would be pulled by indaviduals name entered by the user of the DB. I can't remember the santax to use in the critera field.
Hi all, I am a new user to Data Access Pages, I have no idea what I am doing. I have been reading some things through this form and have not found anything really of any use. So I am asking myself if anyone can help me. I have a database all ready created and I would like to change that into Data Access Pages. I have file save as my Form to Data Access Page, however when I look at it in webpage view the command buttons dont work. I have no idea what is going on with the code. could anyone help me please, thanks in advance.
