Hi
I am creating an employee db, with fields including employeeID, surname, firstname, partnerSurname, partnerFirstname, partnerID. As some partners work at the company, I want the partnerID to list (thorugh a value box) employeeID's that match surname to a partnerSurname and firstname to partnerFirstname.
Is this possible? Any help greatly appreciated.
Thanks in advance.
i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
View 2 Replies View RelatedI'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.
Code:
Public Function GetID_PatientStudiesGroup&()
GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup]
End Function
Private Sub BPRS_T1_Button_Click()
Call GetID_PatientStudiesGroup&
Dim strSQL As String
[Code] ....
As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.
Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
My thinking must not be right as I can't get this to work but i have a table with
Date AMT
02/05 50
02/06 20
and soforth....
I am summing up each week and putting this in a table with values
WeekBeginningDate WeekEndingDate SumofAmt
I am trying to do
INSERT INTO tblTwo ("02/05/2007", "02/09/2007", SumOfAmt)
SELECT sum(Amt) FROM tblOne
where TDate >=#02/05/2007# and TDate <= #02/09/2007#
The two dates in the INSERT INTO are just values and the SumOfAmt is from the select. Can someone help me with this? Thanks in Advance!!
Hi, this is my first post in this forum... so thanks all for your answers...
Ok, the problem is this...
I've got a database with some tables, but in that database i've got 3 important tables....
Lets refer that the tables have this names [Table1,Table2,Table3];
In the Table 1, there are 3 important variables, one of them its a primary Key, ok all i wanna know how to do... its this:
When i insert a new regist in this table, the first 3 values from the first three colunns must be replicated to table2 and table3.
Can you help me PLEASE!! :(
Hi All
I have a list box. Below it lies a text box.
I'd like to click on multiple values in the list box which then populates a text box with each value to form a sentence.
currently my code looks like this
Dim Comment As String
Comment = Me.lstComments.Column(0)
Me.txtComment = Comment
Using the above method only inserts one value. How can I modify this to insert multiple values into the text box?
Much appreciated
I have a table which holds the status of peoples orders. It holds information on order_id, order_status, order_desc, letter.
A letter is printed for each of the different statuses. However for one status i need two different letters to be printed.
How can i insert two values into the same cell.
So far i have done
insert into order_status (order_id,order_status,
order_desc, letter) values('025', 'Dispatched', 'Black tailored coat ','DPT notification');
This prints the DPT notification fine but i need another letter to print along with this.
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load()
If Me.RecordSource <> "" Then
If Me.Recordset.RecordCount = 0 Then
Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
I want to insert obtain marks of the subjects. Find the attached my db. when i select combo value my requirement is to insert the all records which associate with the combo. but when i select combo value it will happen nothing.
View 7 Replies View RelatedHello guys,
Does anybode has any idea, of how to do that ? I can do it very easy in VB.NET, or C#, but in Access I give up.
So, I have to take data from the form , and send it to Sub.
With data type of String , I have no any problem, use Nz funciton, end everything, goes well.
But with Date and Integer, I can not find solution.
So :
Date
Dim PensionerFromDate as Date
PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)
(instead of ???? i tried everything .. dbNull, vbEmpty, vbNull,sqldatenull, and somtimes it works but int the table stores "12/301899")
Integer
Dim CompanyID as integer
CompanyID = IIf(Me.cboCompany.Column(0) = 0, ????? , Me.cboCompany.Column(0))
I tried here instead of ????, tu insert "", ",," , " " , Cint("") itd. itd. . but nothing works.
Has anybody any idea ?
100 x thanks in advance
i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.
i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.
(ps: in my property sheet for my lstCuisine listbox its multi select is simple)
Here is my codes:
Private Sub Add_Record_Click()
If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If
Dim conceptValue As String
[Code]...
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
i want to add a control in that form that sums up and aggregate all the values of field called [amount] based on the value of [Name_Patient] as criteria
View 2 Replies View RelatedThere is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
What is the best way to proceed?
Thanks for your help.
Adrian
Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of the information.
Thanks
On the creation of a reconrd in the main table I'm trying to auto fill a sub table with standard information for the user. This is what I'm using to insert into the sub table:
db.Execute "INSERT INTO tblHistologySlides (HistID, HistSlide, HistEntBy) VALUES (" & Me.HistAutoNo & ", " & Me.Text58 & ", " & Me.HistEntBy & ")"
It's stepping through the code without problem but the data isn't going into the table.
Any ideas where I'm going wrong?
Thanks
D
I have an access database that contain 2 tables and 1 form :
table1 named customers (custid-custname)
table2 named sales(salesid-custid)
form named form1 contain (salesid-custid)
*note:in form1 custid is combobox
What i need is when i select an customer from combobox the column custid in sales table get custid not custname
I use this sql statement to get combobox items from customers table
Code:
SELECT Cust.[Cust-Name] FROM Cust;