Adding / Removing And Saving Existing Query
Jan 28, 2013
I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.
Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?
When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state.
Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.
View Replies
ADVERTISEMENT
Mar 15, 2014
Is it possible to add a query to an existing report? I use Access, not VBA.
View 1 Replies
View Related
Nov 12, 2014
I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?
The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.
View 6 Replies
View Related
Dec 2, 2012
I am trying to add a query in an existing form. When I click on adding a field, I get an option to show "all tables" for not queries.
View 3 Replies
View Related
Oct 6, 2005
I am a new MS Access user. I am having difficulty adding columns to a pre-existing database. Is there a simple way to do this?
View 4 Replies
View Related
Jan 24, 2007
Hi.
I have a table with a NAME field and a GENDER field.
How would I code a query so that if the gender is MALE than MR. would be added to the front of the existing data in the NAME field? MRS. if gender is FEMALE?
i.e., the result would be MR. SMITH, MR. JONES, MRS. CARSON...
Russ
View 4 Replies
View Related
Mar 27, 2007
Please bear with me, first post, trying to get to grips with Access for work!
We (a primary school) had a "bespoke" database set up using MS Access by someone who has long left the area and now we need to make changes and I, as the most computer literate person on site, have been volunteered to make these changes.
My level is beginner (for now) and my first job is to add some new options to a "Titles" table, four columns, six records. Column1 = ID#, Column2 = Male Titles, Column3 = Female Titles, whilst Column4 seems to hold items from Columns 2 & 3.
If I add a futher option, I get the error "Can't perform cascading operation: Entry must be one of the Titles as featured in the drop-down box or in the Titles table.
Now, as far as I can tell, I HAVE added the new option to the Titles table, and they DO show in the drop down box in the main spreadsheet display, BUT the error comes back as soon as I try to confirm the new title.
Any ideas please?
View 9 Replies
View Related
Sep 28, 2005
Help!
I found this wonderful code and would like to add Proper Case upon save. Have tried several variations to no avail. Any help would be appreciated.
Private Sub cboDayshiftPatent_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf
'connection and recordset object variables
Dim cn As Connection
Dim rs As ADODB.Recordset
' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "lkupDPatent", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("Dayshift") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
End With
Response = acDataErrAdded ' confirm record added
Else
Me.cboDayshiftPatent.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If
Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set cn = Nothing
Exit Sub
Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
View 1 Replies
View Related
Dec 8, 2011
I was tasked by my CFO yesterday to add a new element to my existing database. I will try to keep what I am trying to do simple.
Basically we do finance for multiple projects. each project has employers.
My database records these employers names, SSN, dates registered, program they are in, and a few other elements as well.
What I have been tasked to do is create a new table with the project managers in that table. this way in a query or report i can pull "sally sue's" projects or whoever I needed to. There are many PM's who have more than one project.
Here is what I have for relationships:
On the Project Managers table I have a PK for ID and then the PM Name as a second field. the ID field is linked to the Programs table which has a PM ID and Program name
The Program table is also related to the employer table which has all of the employers and their info. the relation between the two tables is the Program Name.
So when I run a query or report my goal is to be able to pick any project manager and get the associated count of employers under all of that projects managers programs. I have gotten errors when running queries. Perhaps I have the relationships set up wrong?
View 1 Replies
View Related
May 19, 2014
What I have is a database that I have done some tweaking on and in the meantime the original db has been in use which has added around 200 or so more records in the table.
What I would like to do is to just update the db that I have been working on with the older db table(the one who has the additional 200 records).
EX. DB A(Old DB, Newer Table) DB B(New DB, Older Table)
I want to put DB A table into DB B
Is this a simple fix? Or do I need to write some sort of query to update the records in the old table? I've tried to export the excel file and then import but it puts it in unrelated objects and then my switchboard or nothing works.
View 9 Replies
View Related
Sep 13, 2012
I have 3 tables
tblProductInfo
- ProductID
- ProductItemNumber
- JDEDescription
tblFacility
- FacilityID
- FacilityDescription
tblProductFacilityMM
- ProductToFacilityID
- ProductIDFK (combined with FacilityIDFK to make a PK)
- FacilityIDFK
As I'm writing this out, I am realizing that tlbProductFacilityMM.producttoFacilityID is probably not necessary, but that I don't expect that to have much significance to the issue. So I've setup a query between the two tables:
Code:
SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductFacilityMM.FacilityIDFK, tblFacility.FacilityDescription
FROM tblFacility INNER JOIN (tblProductInfo INNER JOIN tblProductFacilityMM ON tblProductInfo.ProductID = tblProductFacilityMM.ProductIDFK) ON tblFacility.FacilityID = tblProductFacilityMM.FacilityIDFK;
And used it to create my subform which is simply a drop down box for tblProductFacilityMM.FacilityIDFK. My main form is one that has already been in use for 6 months or so, it is based off the tblproductinfo table and needs to have the option to select multiple Facilities for each ProductID. I inserted the subform, but when I try to select a facility I get an error that reads:
Quote: Cannot Join Records; Join key of tblProductFacilityMM not in recordset.
View 2 Replies
View Related
Apr 3, 2014
I'm having a bit of a problem with a database I'm trying to create, what I am trying to do is create a database to handle jobs in an IT workshop. The idea is that you generate the job card and then at the bottom you have a section to enter in the parts that are required for the job, and then have a report that prints out a list of all the parts required for all the jobs under a heading of the job card number.
The problem I am having is how to add this sub form / table to the job card sheet. I tried putting in a sub table, but that wont let me make additions to it, and I tried putting on a form but that wont retain more than one entry per job card (I have put it on continuous form).
View 2 Replies
View Related
Jan 31, 2012
I have a simple database that I inherited, that contains basic customer contact info. We want to start adding more functionality to the database, so I've done alot of research to learn about how to begin normalizing my data.The existing table does not contain a useful primary key, since we may have multiple individuals from the same company, companies with very similar names, etc.
I would like to add an AutoNumbered field to use as a CustomerID/primary key, but I can't seem to find a way to do so. Here is what I've tried so far:
I have tried to add a field to the table, and make it an AutoNumber data type. When I do so, I can error message saying that I cannot make a field AutoNumber if any other field in the table already has data entered in it.I have tried to create a new table containing only an AutoNumber PrimaryID field, and then import data from the existing table, but that just creates a 3rd table.
View 1 Replies
View Related
Jun 3, 2005
Hi,
I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how.
Thanks
zorter8
View 1 Replies
View Related
Jul 29, 2014
I have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.
Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?
If I run an update query I will have to manually add each field to the query.
The update query will not add 'new' records. If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).
If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.
View 1 Replies
View Related
Apr 17, 2012
How to add a prefix to an existing description in a project table, generated from the project number. Here's a sample of my data:
PROJECT NUMBER DESCRIPTION
01200000 Completed Projects
01601530 Steele Sub
I would like the Descriptions to read:
01200000 MO-20 Completed Projects
01601530 MO-60 Steele Sub
The state, "MO," comes from the first two digits "01" and the coop, "60," comes from the second two digits. I have a table of a thousand or so projects in an Access database that I need to amend the descriptions of to include these prefixes. Is there a simple way to do this in Access or in Excel without writing code?
View 1 Replies
View Related
Jun 8, 2014
I'm trying to add a combo box field to an existing table and form. I added the new field to my existing table and set it up as an combo box added my row sources. Then added the new field to an existing form and now am getting a error stating "The record source "Table name and added field here" specified on this form or report does not exist. When I just make the new field a text box instead of combo box it works just fine.
View 2 Replies
View Related
Nov 12, 2013
I have a command button that basically saves the record that i just finished entering. here's the code:
Private Sub cmdAddAnother_Click()
On Error GoTo Err_cmdAddAnother_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_cmdAddAnother_Click:
[Code] ....
What I'd like to do now is add a second function that will make all the controls on the form go to null after the record is saved. so the user can start from scratch and add another record.
I tried adding me.refresh right before "end sub" but that didnt work.
I tried adding "me.controlname.value = null" for every control on the form, and that didnt work either.
I also tried adding this code that i found on bytes.com:
On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
ctl.Value = Null
Next
That didn't work either.
View 4 Replies
View Related
May 13, 2015
So, I have two forms that I am trying to make work the same way.
With F1Entry I can use the combo box in the header to select different request numbers.
With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.
Database attached
View 12 Replies
View Related
Nov 26, 2013
I have a form to add a record to a table. How can I give the user a way to close this form without saving the just created record.
I tried
If me.dirty then
DoCmd.RunCommand acCmdDeleteRecord
endif
This works, but gives a messagebox in return to confirm the command, and I don't want that. And I try to avoid sendkeys. I also don't want to change the options of access.
View 2 Replies
View Related
Dec 3, 2013
I am trying to open a form in design mode and add controls, this i have done. I am doing this all in a class, and am having trouble saving the modified form.
I have tried using the following, both produce errors
Code:
DoCmd.Save acForm, "tmpQueryDes"
DoCmd.Close acForm, "tmpQueryDes", acSaveYes
View 3 Replies
View Related
Aug 14, 2006
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
Any suggestions appreciated
View 1 Replies
View Related
Oct 3, 2005
I have a date field that shows the date like 12-Sep-05 (medium date). I have a report in which the date has to display without the placeholders (e.g., 12Sep05).
I believe what I need to do is create a field in the query to remove the placeholders but I don't know how to write the code. Will anyone be willing to show me? Any help would be appreciated.
View 2 Replies
View Related
Jan 25, 2007
Hi,
just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.
What I want to display is only one record per entry containing an exact match for criteria X.
Finding it hard to articulate this but I would need something that defines:
If criteria X is the same, just display one record.
Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.
Thanks
View 3 Replies
View Related
Apr 24, 2014
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
View 8 Replies
View Related
May 11, 2015
I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:
DATE ID
01/01/2015 1
02/01/2015 1
What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.
I'm unsure what the easiest way to do this would be (or how to do it at all).
View 2 Replies
View Related
