I'd like to create a query that would do something like the following:
If Code="A" then ProdType="Accessory"
If Code="BS" then ProdType="Blank Stock"
etc.
Can this be done without creating a query for each instance?
Thanks.
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;")
Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
sqlStatement = "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;"
Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)
saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database
'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:
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
strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL
End Sub
It keeps saying "Run-time error '3292': Syntax error in field definition.
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
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.
HI all-
I have a list of INSERT statements (SQL DML) and I wish to insert this data into an MS Access 2000 table
Unfortunately I cannot find a mechanism to accomplish this in Access. Does one exist? How can I import this data into access in a SQL format?
Thanks in advance!
When the IVA_MEMBER_ID field has no data I would like this query to return : 000000000,
is this possible and if so could you show me how? Thanks
SELECT DISTINCT
NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN,
NOLDBA_LOG_DISBURSEMENT_DETAIL.AMT_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_TYPE_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_ORDER,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_OBLIGATION,
NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE INTO [A TBL]
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;
I want to set my insert such that
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.
Access says I'm not writing an updateable query.
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...
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.
Does anyone have any ideas?
Kind Regards
Keith
when I am exporting in fixed width format (.txt file), is it possible to automatically include in the outputted text file:
a header of the current date and time, and
a footer that will say how many records are in a certain table?
edit: when i say header and footer, I mean the first and last entries in the text file
thanks
Venom
Is it possible to select some data in queries in the same db to a table?
Many thanks!
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).
Tanks
i would like to create an excell file (and choose the path) in ms access
and fill that excell file with data divided over multiple sheets
or even if possible take an excell template and fill it with data, then rename
and save it
any suggestions?
Inserting data from History Table LIKE "P" Type into Preventive Maintenance Table and LIKE "R" Type into Repair Table.
I don't quiet get it because it says "PM Data Update" but nothing happened. Below is the code:
Code:
Private Sub cmdUpdate_Click()
Dim strSQL
Dim dbMNT As Database
[Code].....
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
Hi All
I am new to this, hope you can help!!
I have 2 tables,
Customers and Routes
I want to auto insert data from Routes when I select the route Number from Customers,
the tables to auto update are
CruiseNumber:
CruiseName:
CruiseDate:
from
RouteNumber:
RouteName:
RouteDate:
I have read HELP in Access but I am a layman when it comes jargon. 'from the many to the few'??
Hope you can help....in 'Plain English'
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.
PHP Code:
Dim strSQL As String 'Add Absence Data to tblHour.
strSQL = "INSERT INTO tblHour (WorkDate,EmployeeID,Hours) "
strSQL = strSQL & "VALUES (#" & Me.AbsenceDteTo & "#, '" & Me.EmployeeID & "', '" & Me.txtAbsHrs & "')"
CurrentDb.Execute strSQL, dbFailOnError
[Code] ......
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
[code]....
I have written a html page that posts a form to a .asp page, which in turn is supposed to write data to a MS Access database. Problem is while my html page works, and my .asp page seems to work fine as well, nothing is being saved to my database!
I'm not sure if its something wrong in my .asp page...
Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Option Explicit %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
[code]....
or if you think there may be some configuration in my database I'm missing. Just an FYI all the database table columns take empty values.
I have problem with inserting sub-form data to specific table. i have 2 table and one form.
table A is for DLOOKUP, table B is the table i want my sub-form data to insert in.
my table A have ID,name and class.
my table B have ID and other column.
Table A' ID and Table B' ID is related.
i trying to insert my sub-form to table B instead it insert the data to table A.
How Mark disappear field data based on field data last.
Example:I have a field type in the name and on behalf of another field No.
In the case of the Type-B data is deleted Number field, which is before the character.