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.
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1 Private Sub Save_Record_Click() 'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information") If messageusr = vbYes Then
Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
'Clear fields on form to indicate write has occurred txtserver = "" cmblocation = "" cmbtype = "" cmbsize = "" cmbassign = "" txtrequiredby = "" txtrequestedby = "" txtrequest = "" txtnotes = ""
'Close recordset and database saverecord.Close db.Close MsgBox "This information has been succesfully saved" End 'return user back to form End If
End Sub
and I have even looked into an insert sql statement [B]Method 3 Dim SQL_Text As String SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &" INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;" Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message Run time error '3061' Too few parameters. Expected 3. :confused:
now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.
I hope someone can point me in the right direction
FROM NOLDBA_LOG_DISBURSEMENT_DETAIL INNER JOIN (NOLDBA_INT_CASE_MEMBER INNER JOIN NOLDBA_INT_MEMBER_DEMOGRAPHIC ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) ON NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CHECK_RECIPIENT = NOLDBA_INT_CASE_MEMBER.MEMBER_ID
WHERE (((NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN)>"0") AND ((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="C") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_CHECK_RECIPIEN T)="1") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE) Between [START DATE] And [ENTER END DATE]))
ORDER BY NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID;
Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB) WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)
Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.
Hi experts. I want to learn how i can insert data in to external db using vba. For example , i want insert table names in to the external db. (A command button in a form )I be happy if some one show me how. Thanks
why do i get all the weird problems? does anyone have any idea why a textbox would act like it was locked, even though it's not?
anyway, the text box works fine, until i click my button which goes to a certain record based on my listbox. the code for the click event is as follows:
Code:Private Sub cmdFindProcess_Click()blnexist = True Dim rst As dao.Recordset Dim strSearchName As String Set rst = Me.RecordsetClone strSearchName = Str(Me!List26.Value) rst.FindFirst "ID = " & strSearchName Me.Bookmark = rst.Bookmark rst.Close If Me.List26.ItemsSelected.Count = 0 Then MsgBox "Please select your process", vbOKOnlyElseMe.formattedtimeelapsed.Visible = True Me.StartTime.Visible = True Me.EndTime.Visible = True Me.btnStartStop.Visible = True Me.btnStartStop.SetFocus Me.StartTimeLabel.Visible = True Me.EndTimeLabel.Visible = True Me.InProcess.Visible = False Me.Current.Visible = True Me.NewProcess.Visible = False Me.hidID.Value = Me.List26.Value Me.formattedtimeelapsed.Value = "00:00:00" Dim Rc As dao.RecordsetDim Db As DatabaseDim SQL As StringSQL = "SELECT * FROM StopwatchRecord Where StopwatchRecord.ID=" & Me.List26.Value & ";"Set Db = CurrentDb()Set Rc = Db.OpenRecordset(SQL)Rc.MoveFirstMe.hidID.Value = Rc.Fields("ID")TotalElapsedMilliSec = Rc.Fields("ElapsedTime")Me.ElapsedTime.Value = Rc.Fields("ElapsedTime")Me.Comments.Value = Rc.Fields("Comments")Me.StartTime.Value = Rc.Fields("StartTime") Me.txtProcessDisplay.Value = Me.List26.Column(3) Me.txtActNumberDisplay.Value = Me.List26.Column(1) Me.Comments.Enabled = False Me.txtAdditionalComments.Visible = True Me.txtAdditionalComments.Enabled = True Me.txtAdditionalComments.Locked = False End If End Sub my guess is that the "bookmark" code is not allowing it. i'm not sure however, how i could find the record any other way, if that is the problem....any suggestions would be greatly appreciated. thanks in advance, *j
I have a form with a sub form, on the main form I want the user to select a machine number and then when they have selected it I want to populate the required data into the sub form.
In the sub form I have a field called "Seal" and another called "Quantity" what I require is to get the results from either a query or table and list all the seals & quantities for that machine.
I have a table called "Machine" which has all the machine numbers and also fields called "Seal1", "Seal1Qty","Seal2, "Seal2Qty" all the way to Seal 10.
I do not know how the best way to get this data into the sub form...
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
Hi, I have a form which records computer faults reported by users. On this form there is a subform for comment tracking eg. Phoned user, Date/Time. On the next line would be; Informed user to do this, Date/Time and so on. I have a button on my form which I would like to use to automatically put in a line of data. It is an aknowlegde fault button. What it hopefully would do is when you click the button it will automatically put in the date/time in the subform, Then the word "acknowledged" in the comment field, then prompt for the user name ( for the username field). I am not sure on how to start the command. If i were to do it in the same form then I would just put something like; me.Date.Value = Date() me.Comment.value = "Acknowledged" me.Username.value = "[Prompt]"
As you can see I have two problems though; 1. The data has to be entered in a subform 2. My Prompt I dont think will work.
My application extracts data from a SQL server according to some filters and put it in a local database (MS Access), I actually do it in two steps, data extraction and loop tru the data sending it to MS Access with insert values instruction, is there a better way to do it (the routine is too slow for large databases, the string convertion make it slow I beliebe)?, I was thinking on something like "Select into", but I am not copying a database and I need to add 2 more columns to the database (add fields that you have at the moment to make the select but aren't on the database and were needed to make the selection).
I have a combo box Customer_Name on Order Form; and I want it open the Customer Form when an user insert a new customer to input all data. This seems simple at first, but problems are these:
1- whenever an user opens Customer Form on a new customer, the user would have to make the input once again (at least retype the customer's name on the field).
2- since the Customer_Name and other fields are required in the table, the Order_form would not allow the user to close it unless all field are filled properly.
Dilemma is you have to fill in the Customer_Name field before the table refresh all records and display it in the combo box on Order Form
I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form? Thanks for your help.
I have a report, which is based on query that links various table.
There is also a form, which starts when the report opens, which has dual function: 1.) user selects criteria to filter out only particular customer but 2.) is for user to add some new data that are not available in query/linked tables.
I need to be able to create now a command button that would add all records from the report to for example 'Report Table'. My report has a tabular form but I have also lots of text-boxes in Report Header that would need to be added too.
I am new with ms Access. I am using 2013 version and stuck with "pretty" easy task.
When I create the form, the values are inserted/updated constatly as I leave the input components. However I would like to insert/update the record only when I click the button. How to do that?
I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.
Code: Function fDAOServerRecordset() Dim db As DAO.Database Dim dblcl As DAO.Database Dim rssql As DAO.Recordset