I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.
FORM
frmAppendSOPTraining
lstEmployees (unbound)
cboSOPNumber (unbound) (the numbers are pulled from an SOP table)
txtRevisionNumber (unbound)
txtTrainingDate (unbound)
cmdAddRecords
I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended. :(
Private Sub cmdAddRecords_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Set frm = Forms!frmAppendSOPTraining
Set ctl = frm!lstEmployees
strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES ("
strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "
For Each varItem In ctl.ItemsSelected
strSQL2 = strSQL & ctl.ItemData(varItem) & ")"
CurrentDb.Execute strSQL2
Next varItem
End Sub
hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.
I am trying to do an append table but i keep getting an error message Pricing ID is the pk autonumber in tblPricing (parent table) historyid is the pk in tblHistory and pricingid the fk(child table) oldprice should be a currency value the other fields are text.
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
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.
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
I work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.
Because the source table of the query is also updated.
I upload the database with a table in it and a query. I want to add the data of this query to another web table....
is it possible to have an insert query to fill some of the parameters by select query from other table and rest of the parameters by passing the value.
ex: insert into table1(a,b,c) values (10,select b fom table2 where.... , 35);
i tried with the above syntax but was unable to succeed.
if this is not possible can anyone help me in solving the problem
I'm trying to write the VBA code to insert data into an audit table. My code is in a function which will have the values to be inserted to it passed to the function.
My function code looks like:
Function InsertAuditRecord(stAction, stTimestamp, stUser) As String
The values "stAction", "stTimestamp" and "stUser" are the values passed by the calling procedure, but when this query is executed, the Enter Parameter Value window pops up, asking for the values for each of these 3 variable.
Can anyone tell me how to code the query so that it will use the values passed to the function?
I have an INSERT INTO query that inserts multiple records in an excisting table.
What i need is a functionlity that fills a column called, lets say 'myID' with a query-unique increment.
So, when the query inserts 50 records the ID should increment from 0 to 49. The next time i might insert 23 rec's and then the ID column should go from 0 to 22.
I tried to call a simple VBA function in the SQL query that returns i+1 and increments i, but that code is only evaluated once every run, so that doesnt work.
I am executing a query and I want to force a value of ALL into a additional field called BUKT. How would I go about this. I need to do something similar to DATE: Now() but with a set value.
Hi I am creating a stock database and I have come across this problem:
I have a stock table, and I have successfully creted a query to insert a record with values selected in a form:
EG. In the form you can input a part no, comments and the quantity you want to add. I have created the stock system so that each individual item of stock is one record in the stock table.
What I can't seem to do is use the quantity field in the form to dictate how many times the form details is entered into the stock table.??
I have an insert query that works fine when all fields are entered: INSERT INTO tblMaster (Fname, Lname, Address, Address2) Values ('Cozmo', 'Kramer', '1Main', '2Main');
BUT...if I have Address2 as blank b/c its an optional field I get a validation rule error...even though there is no validation set up on this field in the table.
I was wondering if i could get some help here. I got error message saying "user defined - typed not defined" and it's highlighting the first line that is WorkBase as Database.
Here is my code. Dim WorkBase As Database Dim WorkRS1 As Recordset Dim rsNew As New ADODB.Recordset
Im trying to do an insert an an update in the same function,but it only allow an upate only if a record exist. Here is my code: Sub insert(ByVal UserSelection, ByVal Grand_Prix_ID)
'The date function 'IF the 2 or more days left before the race then ' Do the insert CompareDates(User) Dim mysql As String Dim strConn As String Dim MUser_ID = Request.QueryString("UserID") Dim Nickname = Request.QueryString("name") Dim LastGP_ID As Integer = Grand_Prix_ID - 1 strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Server.MapPath("App_DataFantasyF1.mdb") & ";"
'else if already selected before do the update Else MsgBox("You have made the selection before and You are about to update", MsgBoxStyle.YesNo) mysql = "UPDATE TeamSelection SET Grand_Prix_ID = @Grand_Prix_ID, User_ID = @User_ID, Driver_ID1 = @DRIVER_ID1, Driver_ID2 = @DRIVER_ID2, Driver_ID3 = @DRIVER_ID3, Driver_ID4 = @DRIVER_ID4, Driver_ID5 = @DRIVER_ID5, Driver_ID6 = @DRIVER_ID6" & "" mysql = mysql & " WHERE User_ID =" & MUser_ID mysql = mysql & " AND Grand_Prix_ID =" & UserSelection(7) & ""
Dim Myconn As New OleDbConnection(strConn) Dim objComm As New OleDbCommand(mysql, Myconn) Myconn.Open()
I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:
I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.
My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.
Anyone know how I can go about fixing this?? Thanks!! Nick
hey all, In my database (For a doctors practice) I have individual patient records with information about them on there. There are 5 boxes showing the current medication, if any, that they are on. The form is all linked up and so when I go through each record for each patient all the data changes as it should. I have a seperate table with all of the drug information on it and I want to be able to click on the box on the patient form (One of the boxes that has one of their medications in) and I want it to be able to automatically put the name of that drug in a search query and bring up the results in a report or form view. Is it possible to do this?
So far I have managed to create a control button on the pateint data form and when clicked, it asks for a a parameter value i.e. the name of the drug. I'd like to be able to miss out this step and for it to automatically enter the name of the drug from the text box into the serach criteria.
Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?
I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
