I have the following tables in my Access database.
A < B < C < D
(The "<" represents a one to many relationship.)A given row in table A can have up to 4 children (stages) in table B (stages 1 to 4).The other one to many relationships do not have any limitations as far as the number of children are concerned.All tables have AutoNumber primary keys.When the user clicks a button in a form, I want to:
Copy all data in the current stage (current row) in table B (corresponding to a given parent row in table A), to the next stage in table B.All data in child tables must be included in the copy process.In other words, for a given row in table A, by use of buttons in the forms for each of the stages 1 3, the user shall be able to do the following:
all data for stage 1 are copied to stage 2 (for user modification), then
all data for stage 2 are copied to stage 3 (for user modification), then
all data for stage 3 are copied to stage 4
I have a quotation database running in our company network which we use to send quotes to customers. A quote may have one or more items in it and each item has different prices based on the quantity. Each item will have a record in the database. So, if a quote has five items, there will be five records with same quote number and if 30 items, 30 records with same quote number.
Many times, same enquiries are coming from different sources (customers) asking quotes specially Govt orders. Now we need to take every enquiry as separate and feed separately in the database. Consider the time for a quote which has 30 items in it and each items has 6 level of pricing. When this same enquiry is coming from 12 different customers, it takes a lot of time to feed it in.
Now my question: How can I copy all the records belongs to one "quote no" and add it to the same table with a different quote number and some changes to the other customer related fields? If I can do that, I just need to change the customer address and I am ready with the second, then third and so on. I want to copy all related records, change quote number and related fields and then append to database.
Also I need this when I revise a quote. The system needs to keep the old quote as well as the revised quote for future reference. If I can duplicate it in one command, I just need to do the changes, Revision Number, Revision Date etc. and the revision is ready to go. Now I use update querries to do changes to all related records during revision. Then I will have only the revised quote in my database and when I want to refer the old quote, I have to refer the hard copy.
I know there is a "Duplicate Record" command available in form level (through wizard), but that duplicates only one record, not a selection of records as I need it here. If this can do with macros, fine. I am not an expert in VB, but if I get the code, I will attach it to a command button and use.
I have a table that holds UK Postcodes and a customer table that holds customers.
I am trying to create a relationship between the 2 so when I enter a postcode in the customer table this is then related to the postcode table.
The main problem I have is that there is a lot of duplicate postcodes in postcode table so the primary key is simply a number as you will see in the picture.
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.
Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.
I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?
I have a query that returns a large data set (~100k rows/month).
I am currently inefficiently exporting week-by-week to Excel to carry out some manipulation (mainly a set of "if" statements to calculate differences between records that have the same site id).
Is there a way to write a function in Access to carry out the calculations? I'm not too familiar with Access VBA apart from coding functions for forms etc.
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
I have a simple line chart plotting price against date.
I would like to plot a secondary line on this chart from an array of data that I calculate. I've searched hi and low but can't seem to find a way of doing this.
The closest I've found is from this:-
[URL]
I can create my array of data but I can't seem to get it to work and think it may be for pivot charts....which access 2013 can't do anymore.
how to plot multiple data sets on the same graph in Access 2013.
I have a form which saves to a database correctly. It has a key field name called "code".
I can recall the data into a copy of the first form to edit by using the key field "code".
All works well, however, when I click save data from the edit form it does not alter the original data but creates another form but with the same key field called code.
so i had created query using query design wizard for which i i needed to enter year of the data everytime the query is being made all my data are correct when just using the query.however when i turning it into report where the query will still be runing and with adding in another field from my original table to get on the montly rearrangement that's where the some record are shown duplicate on the report
I am trying to create a form for data entry. I would like the Total Balance in the last record to become Total Cost in the new record. However, if the total balance in the previous record is =<0 it prompts the user to enter a new total cost else use the last Total Balance. The form in question is FrmSubClients in the attached file.
I have a list of customers with multiple rent start and end dates. I need to create a query where each customer will have a record for every month/year between their respective rent start and end dates. I generally use macros but can't figure out a way to do it without VBA. My field names are Rent StDt and Rent EndDt. My query name is QCalcREndDt. I use version 2010.
Looking for a way to use transfertext to make a table and create a spec file from csv files I am importing. I want all fields in the table to be shorttext.
Otherwise I need another way to bring the files into a table.
I have a generic spec to use that brings in any data as text.
Created the spec from a delimited file with 255 text fields... Field001, Field002, etc.
However the field names need to be changed. My field names are in the first row of a table. I will need to read each fields data in the first row and rename the field.
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
I'm currently working on a project within access which will allow users to select/input information in a form which will then save to the database.
What I need is to create a button on the form which will allow you to convert the data collected from the form to a custom text file template.
(Or maybe the last row added to the database. - although this may cause errors. Example: the form information doesn't save which will in turn create a text file with the data collected from the previous job)
The custom text file template should have a sentence description followed by the value recorded from the form.
Example:
1 configuration type: (data from form)
2 model type: (data from form)
3 Poc: (data from form)
And so on.
So in summary - I would be to use the form to collect the desired information which will save to the database.
Once actioned, a custom text file should be populated with the collected information (with specific data placement as per the example above)....
This is a real mind bender. I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database. I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field. I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.
Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.
I have attached the code below:
Private Sub cmdDuplicate_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset Dim F As Form 'Return Database variable pointing to current database Set dbs = CurrentDb Set Rst = Me.RecordsetClone
I am trying to create duplicate records from a main form frmManagers which has a subform frmSubMeasure. I have placed the duplicate button on the main form. It creates a duplicate of the main form data and gives me the option to add new record to the sub. I want the duplicate to be created on the sub form for me to just edit the scores.
I don't know how to pass the sub form data to be duplicated I thought the append query which I used would update the tblSubMeasure table which created the subform frmSubMeasure. In the sub the append query do update the form with the new MeasureID from the mainform and the form is available to enter new data. I want the subform data to be duplicated as well
In the query I included all the fields from the tblSubMeasure table and this is appended to the same table tblSubMeasure and I place a tag on the MeasureID using "[Forms]![frmManagers].[Tag]"
Code: Private Sub btnDuplicate_Click() Dim dbs As DAO.Database, Rst As DAO.Recordset Dim F As Form 'Return Database variable pointing to current database. Set dbs = CurrentDb Set Rst = Me.RecordsetClone
I have almost no experience of VBA outside of working a little with codes generated for me like docmd etc.
I am trying to create a database for resourcing staff. I have a form where the user inputs a start date and an end date which updates a table. I have a query that works out the number of working days between those dates. I have a table with all the working dates between now and 2016 I want to put a duplicate record button on the form (I can do that bit)
The tricky part is I want it to create a new record for each date in the dates table between the start and end dates. Example 23/06/2014-18/07/2014 = 20 working days. I want to end up with 20 records with start dates as below and the same end date.
23/06/2014, 24/06/2014,25/06/2014,26/06/2014,27/06/2014,30/06/2014,01/07/2014,02/07/2014,03/07/2014,04/06/2014,07/07/2014 etc up to 18/07
I created a database and I manage to split it into front end and backend. now I'm going to make an EXE of the front end.My question is when I open the front end, I need the database to be empty, and them create a button that will make the user select which project he wants to open (backend).I also need to create a button that will create a new empty backend and save it as a new project.
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus() Dim partymsg As Integer Dim dbs As dao.Database, rst As dao.Recordset Dim F As Form Dim intHowMany As Integer Dim intCounter As Integer
create an expression in the Validation section to prevent duplicate entries in a field. Please view Powerpoint file on slide 1 on attachment for More detail instructions. Instructions are in simple Text and images which makes it easy to understand. I am using Access 2003.
Am using a replica set in order to distribute a school reporting system around teaching staff.
Everything works fine apart from one thing. A marking system (basically 1 to 6 list) is stored in a linked table. Before replication this appears in the correct order. Once the database has been replicated however the list is placed in a random order and I cannot rectify this.
With only 6 items this is not much of a problem but I also want to use a much longer list and the same problem appears.
Can anyone explain why this happens and if there is a simple way to get around it?
Am really only an Access novice and I really appreciate any help that you could give me.
I have a db with two tables linked by a Set_ID field. One table characterizes set information (date, time, location etc) and the other table has records for groups of fish caught in the set. The fish_table has (amongt others) a field for species (text), clip-status (yes/no), coded wire tag status (yes/no), and 'count' (number). The count field is necessary to allow input of groups of fish en-masse, or individually, depending on the amount of accessory information obtained.
Obviously, some sets catch no fish and so no record is entered into the fish table for those sets.
When I design a query, I want to produce a table that sums up the count field for each set, and produces subtotals for each species (and for the 4 variations of the clip/tag status fields).
My efforts so far are only partially succesful in that I can produce the correct subtotals, but only for sets where something was caught. Sets with no corresponding fish_table record are ignored instead of treated as zeroes.
Is it necessary to manually enter a 'zero' count for each species of interest for each set that we do? (Massively time consuming) Or is there some other way to query the db that forces the query to equate no fish record with a zero value?
Any thoughts? I've searched the forum, and googled, but haven't come across anything I could recognise as analogous to my dilema.
I have a set of code to keep an audit trail that calls a module:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer) Call AuditTrail(Me, CurrentCYIDPK) End Sub
Calls
Code:
Option Compare Database
Const cDQ As String = """" Sub AuditTrail(frm As Form, recordid As Control) 'Track changes to data. 'recordid identifies the pk field's corresponding 'control in frm, in order to id record. Dim ctl As Control Dim varBefore As Variant
[code]....
how to run both of these events on Form_BeforeUpdate.
I have multiple forms that do just about the same thing. I want to clean up the database so I am going to use one form and switches to vary the data or displays if needed. the form can be opened directly, linked to a button and used as a subform. the form is based off one table, but depending on how its opened, the criteria for which record is shown changes. when used in the subform it needs to be based of a field in the main form. When used on the form with the button that opens the form it is using data from the button.
I have 2 different qry's for each problem. I just can't get the form to pick the correct qry va VBA code. I want to have the forms switch to determine which qry to use.
