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.
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
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.
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 RelatedIs it possible to add a query to an existing report? I use Access, not VBA.
View 1 Replies View RelatedI 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.
I have to build an estimate worksheet from scratch on Access I am almost finished with the project but I have a few questions.
(SEE ATTACHMENT FOR CLARIFICATION)
1. There is a labor section on the form where the user selects the combo box adds the quantity of workers, and number of hours. The output would be what the total amount needed for that trade. On a different table i have the wages of each trade per month that is associated to the form. My problem is if there is a new wage amount for a trade it will affect all my previous records and i do not want that. Is there a way i can set it to only change my new records. In file maker there is something called defined record that is a solution for that program but i was wondering if i could do the same for access or Visual basic application for access. Another solution that I had in mind was like having a button where user could click and all field’s data will not be changed in the future.
2. There is a material section on this form where user input cost of materials. Once user is finished with data entry they click on a button that generates another form for presenting/printing purposes. The thing is that not all fields are always used i wanted to know if there is a way on to adding a flex grid so there would take less space up?
I'm currently building a database and one of the things that I must make is a "warning", more like a symbol on a continuous form, that is there, if the task is wanted by a costumer.
I have a Yes/No field in database, which decides that. Now I need to figure out how to make it appear on form. I thought about making a calculated field, that would display some warning icon.
Now my question is, is it possible to add an icon to a calculated field and if possible, how?
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-Microsoft Access 2010
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
Table1[main]
...account1
...account2
...account3
...dol
[code]...
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies View RelatedI am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF
Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop
rstPrice.Close ' Tidy up
Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.
So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.
The code I am using
sql = " Update crm_order_det SET [Qty] = (" & _
frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
"' and order_id = " & frm![order_id] & ";"
DoCmd.RunSQL sql
However I get the following error
Run-time error '424': Object required.
Like the title says it:
Is it possible to link/export a query to a existing Word document?
I have several Word documents with text, and i want to place the data of a few query's in those documents in a certain place.
Is this possible true vba or another way?
Is it possible to add/edit a record from a DAO querydef that has a calculated field in.
Code:
Set qdf = db.QueryDefs("qryOutput_" & strDept)
qdf.Parameters("Enter Date") = dDate
Set rs = qdf.OpenRecordset()
Using the rs.Edit or rs.AddNew worked fine until I put a calculated field in the query.
The calculated field is just a total of some fields in the table.
I thought this was something to do with dbOpenDynaset but it just keeps having the same error
Cannot update. Database or object is read-only.
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 RelatedPlease 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?
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
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?
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.
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.
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).
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.
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
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.
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?