I already have a form where you specific the date and the store I've purchased something at. I want to be able to add several products I've purchased to a table and define their price, using the same form, so I don't have to input the date and store type for each purchased product.
So, when the form updates the table, it will create a new record for each product, using the product description and price - defined for each product - and using the date and store - defined once - as elements of the record. Is there a way of doing this?
I'm quite familiar with creating tables, forms and queries with the design wizard - not so familiar with using scripts, but I've had a small amount of experience with manipulating databases using MySQL, so I'm willing to learn anything that may be necessary to achieve what I need.
I want to create a button that adds as many records to a table as you want. So I would have - a text field where the user would input the number of records - the code of the button having a for loop or while statement to add that many records as the user typed in
This is my code of a button that adds one record at a time. My VB is not good so could someone tell me how to put in a for loop or while statement that would do the trick? Thanks.
Private Sub addbutton_Click()
If IsNull(Combo0) Then MsgBox "Please select an Oil to add data (Top left corner)" Else Me![Property Subform].Form.AllowAdditions = True Forms![Values Form]![Property Subform].SetFocus
- I have a module which runs queries on linked sales spreadsheets, to merge them in to one Access table. - To prevent duplication of sales, the primary key merges the sales record and item number fields.
Today, it's found 103 key duplication errors, which is fine. But it's still adding data to the table. The data seems to be fields which aren't even mentioned in the query. It only does this when the query is ran from VBA.
I've been trying to get my head round this one , but i'm just to thick to get it.
I have a continuous form that lists all items at a certain location. The user selects a record by clicking on the record selector and then clicks on a command button with this code:
The user then enters inspection details in to the "DivingInspectionCert" form which i want to store in a table (DivingCert) which will relate to the item. On completion of this form the user then clicks on a command button with this code:
DoCmd.RunCommand acCmdSaveRecord
If Me.RecordsetClone.RecordCount = 0 Then MsgBox "There are no items to Print", vbInformation, "EquiTrac" End Else DoCmd.OpenReport "DivingInspectionRpt", , , ("EquipmentID = " & Me!EquipmentID) DoCmd.Close
When i click on this button, the report doesn't print and i get a message "No current record". The inspection details are not stored in the table. I did have the forms RecordSource based on the table "DivingCert" but that didn't work and i have just tried a query but it is still not working.
Would be much appreciated if you can help me. Thank you
How to design a form for a table based all records to design in a single form without top to bottom list wise and without scroll bars. Need to form design for all record details are will show on a form as side by side only as horizontal list wise.
For Ex. I have attached the screen shot image as per horizontal wise records continues.
I've created a button on my ACCESS 2010 form that will send a PDF via email. However I only need to send the single record displayed on the form not all records.
The on click command of the button sends the PDF of the report. (I read that this is what should be sent; but still get the same results.)
I don't understand how and where to attach VBA code:
I Work for an NHS organization. A department here orders prescriptions and sends them out to Gp Surgeries. I have been tasked in designing a database to make this easier for them.
Prescriptions arrive on a Pallet. Each pallet has 100 Boxes of prescriptions on it. Each box Contains 2000 Individual prescriptions. I have a table which contains the following:
Box Number Serial Start Number Serial End Number
When the user enters the first box number and the serial start number, i need it to calculate the serial end number and insert another 99 rows into the table and also calculate the serial start and end numbers for these other 99 records.
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
i have a standard database with tblProducts, order, employees, customers, suppliers etc. i also have a table called tblperson with personID which acts as a one, with the many in three different tables (customers, suppliers and employee) the person table holds data such as first name last name address city postcode and phone. now, ive created a form called frmPerson that acts as a adding data to my database. ive got a load of buttons a working add, delete and a save button, i also have the option to switch through records next, last, end and first. now basically in this form i want to be able to add data i.e. fill out the text boxes and then have a combo box or something similiar that when i add the data i can basically click an option the says 'employee' 'supplier' or 'customer' and then the data gets added to my person table however the difference is that it will also be associated to the record in the wanted table.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
Hi everyone . Please I need help with this I have a db with 22 records: (A123,B456,C789...Z999) I want to be able to read each record and extract from each one the same data fields and make a big single record out of the reading:A23+B56+C89...Z99 The reason of doing is I want to be able to show on a form the data extracted from the multiple reading. I tried a crosstable, but gave no much room to work with. I think if I use 22 different queries it wont look professional and hard to maintain. I've being considering better a loop with maybe multiple if conditions but I dont know how to apply it. Something like newrecord = "" for x=1 to 22 read record x newrecord = newrecord + record x next
I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.
I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.
But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:
first_name | last_name | state_name John | Johnson | AL, AK, TX, FL Eddie | Edwards | MA, TX, FL Sally | Smith | VT, WA, CA, TX
How do I concatenate records from a single column?
Thanks for taking the time to look at my problem first of all. I have a form that shows records but I want a button next to each record that will append that one single record to another table that is built exactly the same, which is for historic records. Not the brightest on Access and I can only manage to append all records.
As an example, I have an employee record that have a task, when the task is complete I want to append the record to a historic table that I can look up later on. However, just that one employee and not all that are in the current table.
I have a tab control with three tabs on it. Suppose I want the user to click a command button to add another tab and then add data to the fields within the tab. How would I go about doing this? Many thanks.
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent EventID(AutoNumber) EventTitle(Memo) 1 memo content 2 data 3 more memo content 4 some info 5 more stuff 6 other text
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
I've recently been told how to enable/disable fields in a form by ticking/unticking a box. Is there a way that, on a tabulated form, i can only disable/enable the field for the same record as the one the check box is in rather than for every record?
I have a form in datasheet view that has a user directory with a list of each person. On each row you can click to open another form that contains the users details using the below VBA:
But once you open to that specific record, no other records are available to navigate to.
I want to be able to stay on that detail form that was opened and go to another record.
How do I continue to pull up that specific user in my detail form, but also load the others so I can navigate to them from the detail form without having to go back out to the datasheet form?
Is there a way to make a form that will display all records based on a single date, at the same time, in the same format each and every time?I have a table which has the following fields:
ID (Autonumber, PK) ServiceDate RunningNumber BonnetNumber Deallocate (yes/no)
Now, I would quite like to keep the form in a style similar to all the others I have, not least as I have to cater for users of all age and abilities, so keeping things as simple. I have attached an image - each row to represent a record basically, I would like the form to open and show the same layout on each day (I would place the textboxes etc in route groups); a null value would not be allowed for at least one field in each record, I could force the records to populate the form in the same way each day?
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons PersonName PersonDate PersonShift Jason 4/10/14 FIRST Jason 4/13/14 FIRST Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
I have an access 2003 database that shows when customer concerns were reported and when thay were resolved. I have a query that calculates how many days it took for each concern to be resolved. I used the query to make a report that shows all concerns which took more than 14 days to be resolved. I want to show these concerns in a chart but it is adding up all the days for each month. For example, if there was one concern in December which took 15 days to be resolved and another concern which took 20 days to be resolved the graph shows this as one concern which took 35 days to resolved. I need to show a bar for each concern seperately instead of showing one bar of alll the concerns for one month. Does anybody know why this is happenning?
I have the following code which is supposed to insert a new record into TBLLetterHistory when a report is printed. The report is based on a Query which selects the records based on their postcode.
What actually happens is that only the first record in the query is inserted into the History Table. Any suggestions would be gratefully recieved as I have no idea, even after long searches in this forum!!
Private Sub Report_Activate() Flag = 0 End Sub
Private Sub Report_Deactivate() Flat = -1 End Sub
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) Dim dbs As DAO.Database, rst As DAO.Recordset Set dbs = CurrentDb() Set rst = dbs.OpenRecordset("TBLLetterHistory")
Flag = Flag + 1 ' If the current value of Flag = 1, then a hard copy of the ' report is printing, so add a new record to the history table. If Flag = 1 Then rst.AddNew rst!ReportName = "RPTLetterByPostcode" rst!Date = Now rst!LeadID = [QRYLetterByPostcode.LeadID] rst.Update rst.MoveNext ' Flag = 0
