Tables :: Syntax Error When Transferring Records With Checkbox Filter?
Jan 8, 2014
I'm trying to transfer records from a table in one subform to another subform (on same form) using a checkbox as a filter:
Private Sub TransferAndReview_Click()
Dim db As dao.Database
Dim strSQL As String
strSQL = "INSERT INTO Forms![Extra Work Report Checksheet]![Equipment Input Subform1].Form[(QuantityUsed[, HoursUsed])] VALUES (QuantityUsed[, HoursUsed]) FROM Forms![Extra Work Report Checksheet]![Equipment Checksheet Table Subform] WHERE [Extra Work Report Checksheet]![Equipment Input Subform1].Form[EquipmentUsed]=True;"
Set db = CurrentDb
db.Execute strSQL, dbFailOnError
End Sub
The Main form is: [Extra Work Report Checksheet]
Subforms are: [Equipment Input Subform1] & [Equipment Checksheet Table Subform]
Checkbox field is: [EquipmentUsed]
I keep getting the error "Syntax error in INSERT INTO statement" which points to 'db.Execute strSQL, dbFailOnError' and I don't understand why. Neither of the fields [QuanityUsed] or [HoursUsed] are lookup fields, etc.
View Replies
ADVERTISEMENT
Mar 9, 2015
I'm experiencing a error when I go to add a filter, "Syntax error in string in query expression "MyFieldName""..If I go to to the table where the field is located, I CAN apply a filter.However, If I throw this single field on a form and switch to datasheet view, all of a sudden I can not filter it.
Additional info: whenever I go to build a query with the field, it throws brackets around this field and no others... this is weird.
Code:
SELECT MyTable.field1, MyTable.field2, MyTable.[problemfield]
FROM MyTable
View 5 Replies
View Related
Aug 10, 2006
I suppose thatīs easy, but I only accomplished one by one by selecting a specific record.
Ty
View 4 Replies
View Related
Jan 15, 2007
I am using DoCmd.TransferDatabase to transfer 2 tables from one database to another. This works fine, however, the user permissions for those tables do not carry over with the table and I have to go into the database and set them as they were in the original database.
Simply, I want to allow complete access for all users to the tables in the second database.
Does anyone have a solution to this?
Thanks
View 2 Replies
View Related
Jul 20, 2015
I want a query that gives me something like this from 2 different tables:
table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02
query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02
Is that possible?
View 3 Replies
View Related
Nov 27, 2006
I understand that any data that is inputted into a form is always transferred back to the table in question.
In the form I created, I had
4 subheading scores added together to form another subheading named function =([1]+[2]+[3]+[4])/4 was the formula that I put into the control source. The calculation worked fine on the form giving me the correct name. However, this number did not transfer back to the table simple being labelled as zero. Can someone please advice!
View 14 Replies
View Related
Oct 31, 2011
The following code works fine
G_StrWhere = "[Group]=" & G_Group_Code & " AND " & "[EntryDate]=" & SQLDate(G_Greg_Date)
Me.Filter = G_StrWhere
Me.FilterOn = True
I would like to get 2 Groups together in the first part using the syntax " IN"
e.g. (4,6 IN G_Group_Code)
View 6 Replies
View Related
Oct 29, 2005
Hello,
I ve got a big problem:) What i am trying to do is CRM , this crm has some informations about the companys. ----->
I have form where are all the informations about companys entered by users of CRM. One of the informations is size of the company. What i did is that i have put three check boxes ( small , middle, large ) and bound them with the main table company ( companyID, companysmall ( YES/NO ), companymiddle(YES/NO), companylarge ( YES/NO ), companyfax, ... ).
When you mark the check box ther is an yes in table for a size of company and no for the other two options.
What i would like to do is to create another form where i could filter companys by ther size. But the tricky part is, that i would like to filter with check box so that i can filtr for example small and large companys at same time.
How can i do this - can you help me - mabe some examples.
Thank you,
Luka
View 1 Replies
View Related
Feb 26, 2014
If I type 2014 instead of b everything works out.
Code:
Dim a As Double
Dim b As String
b = Right(RepMonth, 4)
a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth
View 4 Replies
View Related
Mar 17, 2005
Hello!
i have a small for you probably problem! I tried to find something here but only bits and pieces could help only a little!
well here is my problem!
i have a form with a subform! the subform is a datasheet by a table!
In my main form I have a check box! what I want to achieve is to filter my subform by current date when i mark the check box and remove the filter when i uncheck it!
i could have it done by query or something but it was a last minute idea! thus i would have to change a lot of stuff doing it that way! the table is already filtered by a combo box record selector!
-----------------------
i have used a code but it does not work probably because there is no filter to activate!
Private Sub Today_Filt_Click()
If CheckBox = True Then Me.FilterOn = True
If CheckBox = False Then Me.FilterOn = False
End Sub
So could i put the filter parameter on the vba part???
any sudjestions????
Thank you :)
View 5 Replies
View Related
May 31, 2005
Hey i have this really annoying bug, theres several tick boxes that im using to filter records but if i tick more than one i get a syntax query error? any idea what this could be? i have had several people look at the code and tell me theres nothing wrong.
Option Compare Database
Option Explicit
Const strSQL = "SELECT * FROM issues"
Private Sub filter_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String
If check_os98 = True Or check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strSQL & " WHERE "
End If
If (check_os98 = True) Then
strFilterSQL = strFilterSQL & "os98 = True"
If check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osnt = True) Then
strFilterSQL = strFilterSQL & "osnt = True"
If check_os2k = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_os2k = True) Then
strFilterSQL = strFilterSQL & "os2k = True"
If check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osxp = True) Then
strFilterSQL = strFilterSQL & "osxp = True"
If check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpda = True) Then
strFilterSQL = strFilterSQL & "fxpda = True"
If check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpc = True) Then
strFilterSQL = strFilterSQL & "fxpc = True"
If check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxas = True) Then
strFilterSQL = strFilterSQL & "fxas = True"
If check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxrs = True) Then
strFilterSQL = strFilterSQL & "fxrs = True"
End If
strFilterSQL = strFilterSQL & ";"
Me.RecordSource = strFilterSQL
Me.Requery
End Sub
View 14 Replies
View Related
May 24, 2013
I'm having a hard time with this (what I thought) was a simple filter. I have an unbound checkbox (chkFlag) and an unbound text box (txtfilter). The filter was working fine till I decided to also try to filter on the checkbox.
I'm trying to filter records that either have either a check in the [Flag] field or have some part of the text in various other fields. The problem is I can get the records to filter if the checkbox is checked or on text in the various fields, but not both. I've tried using AND and OR, but it's not working.
Code:
Me.RecordSource = "SELECT * from tblContacts; "
'-------------------
strWhere = ""
[Code]....
View 4 Replies
View Related
Jul 29, 2013
I have a query which is run from a form via a command button. On this form i have a couple control sources (not sure if this is the correct term, but i have text boxes and drop down lists which the query uses in its criteria).
Everything works fine except i can't figure out how to filter my checkbox field (aka MyDeliv). I'd like to have a drop down box with 3 options: "yes", "no", and "ignore". i would like "yes" to return only records with the MyDeliv box checked, "no" to return only records with the MyDeliv box unchecked, and "ignore" to return records regardless of the MyDeliv checkbox.
View 3 Replies
View Related
May 27, 2015
On my continuous form, I have a field that is a checkbox. I would like to place an unbound checkbox/radiobutton in the form header so that when the it is checked, it will display only records that are checked, and when it is unchecked, it will only display records that are unchecked.
View 2 Replies
View Related
Aug 21, 2013
I have a reservation form and I want to tick a checkbox that will filter the form based on what is in the "Reservation Status" combo box.When the checkbox is ticked, the code would remove all records that have "Complete" as a status in the "Reservation Status" combo box. The non-working code that I currently have is:
Code:
Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
If Me.chkHideComplete = True Then
Me.filter = "[ReservationStatus] = 1"
Me.FilterOn = True
[code]....
View 7 Replies
View Related
May 27, 2015
I need to add an additional filter to my unbound form and can't seem to get the syntax right -
I have a text field in my form "[B Style]" that has either a "Y" or "N"
I want to add a checkbox on my main form where, if checkbox=True then [B Style]="Y"
Private Sub ckBStyle_Click()
Dim stFilter As String
Dim stDocName As String
stDocName = "Modify_OpenItems"
If Me.ckBStyle.Value = True Then
DoCmd.OpenForm stDocName, , , ("[B Style]" = "Y")
Else: DoCmd.OpenForm stDocName
End If
End Sub
stDocName is opening but is completely blank.
Incorrect syntax?
View 1 Replies
View Related
Mar 4, 2015
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table
bub|12345|1
bub|45678|2
bub|91011|3
Child Table
bub|45678|2
result in Main table
bub|12345|1
bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
View 3 Replies
View Related
Mar 2, 2007
On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:
Private Sub cmdLotHistory_Click()
Dim stDocName As String, stSelection As String
stDocName = "rptLotHistory"
If Me.rdoAll = True Then
DoCmd.OpenReport "rptLotHistory", acViewPreview
End If
If Me.cboLotLU.Value > 0 Then
stSelection = "[LotID] =" & Me![cboLotLU]
End If
DoCmd.OpenReport stDocName, acViewPreview, , stSelection
End Sub
When I choose the ID and click the button I get the message
Run-Time error '3075':
Syntax error (missing operator) in query expression '(LotID
=020806B1585)'.
the 020806B1585 is my ID number.
Debug hightlights the
DoCmd.OpenReport stDocName, acViewPreview, , stSelection
but I'm guessing my real problem lies in the
stSelection = "[LotID] =" & Me![cboLotLU]
Can anyone point me in the right direction?
Thanks,
Rick
View 2 Replies
View Related
Sep 12, 2005
I have a function that builds a filter and it get a syntex error. It has three components and I must be missing something in combining them into the filter. SpecID and ReviewID are numbers. Selected is a checkbox and 'Yes' is a string. Can anyone see the source of the Syntax Error?
Private Function PlanFilter()
Dim strFilter1 As String, strFilter2 As String, strFilter3 As String
strFilter1 = "[SpecID] = " & [Forms]![frmMainEntry]![SpecID]
strFilter2 = "[ReviewID] = " & [Forms]![frmMainEntry].Form!fctlReviewRequests!ReviewID
strFilter3 = "[Selected] = 'Yes'"
gstrFilter = strFilter1 & " And " & strFilter2 & " And " & strFilter3 & ";"
Debug.Print gstrFilter
End Function
Thanks,
PC
View 3 Replies
View Related
Jun 20, 2007
I'm running a VBA query in excel trying to import the field from a table, simple so I thought, any idea on why i'm getting syntax error codes on this part?
Application.StatusBar = "Retrieving Depot Names"
sSQL = "SELECT tbldepot.DepotName 'Depot' " & vbCr & _
"FROM tbldepot, " & vbCr & _
"GROUP BY tbldepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2
same thing on
'-= DFE =-
'Some manual DFEs have auth code but left at created
'AAP children have the DFE, not parent
Application.StatusBar = "Retrieving DFE Values"
sSQL = "SELECT tbldepot.DepotName 'Depot', " & vbCr & _
" SUM (di.Qty * di.Rate) 'DFEVal' " & vbCr & _
"FROM tblA537 a, tblA537DFE d, tblA537DFEItem di, tblDepot " & vbCr & _
"WHERE tblDepot.DepotID = a.DepotID " & vbCr & _
"AND a.OrderNumber = d.OrderNumber AND d.DFEID = di.DFEID " & vbCr & _
"AND NOT d.Authorisation IS NULL AND d.DFEStatus<>4 " & vbCr & _
"GROUP BY tblDepot.DepotName " & vbCr & _
"ORDER tblDepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2
View 5 Replies
View Related
Jun 26, 2007
Hi All,
As I am really 'Green' in this line, can anyone tell me what wrong with my syntax?
Thanks in advance.
INSERT INTO Depreciation (AssetID,DepreciationAmount,DepreciationDate)
SELECT Assets.AssetID,
CASE WHEN Assets.AssetID FROM Assets not in (SELECT Depreciation.AssetID FROM Depreciation) THEN Month(Assets.DateAcquired)*SLN(Assets.BookValue,As sets.SalvageValue,Assets.DepreciableLife)/12
ELSE SLN(Assets.BookValue,Assets.SalvageValue,Assets.De preciableLife)/12
END,
Format(Me!DepnRunDT, "0")
FROM Status INNER JOIN Assets ON Status.StatusID=Assets.StatusID
WHERE ((Assets.StatusID)=1);
View 1 Replies
View Related
Sep 20, 2007
I am trying to create a query using the SQL view, i get a missing operator error while running this sql
SELECT tblPDInv_Temp.CustId, tblPDInv_Temp.CustName, tblPDInv_Temp.OnHold, tblPDInv_Temp.InvID, tblPDInv_Temp.InvAmt, " & _
"tblPDInv_Temp.AmtPaid, tblPDInv_Temp.DueDate, [InvAmt]-[AmtPaid] AS AmtDue, tblPDInv_Temp.InvStat " & _
"FROM tblPDInv_Temp " & _
"WHERE ((([InvAmt] - [AmtPaid]) <> 0) And ((Date() - [DueDate]) > 0)) " & _
"ORDER BY tblPDInv_Temp.CustName, tblPDInv_Temp.CustID, tblPDInv_Temp.InvID;"
Thanks
View 2 Replies
View Related
May 5, 2005
I use the following code in the After Update Event of the field Lastname to check for a possible duplicate record:
If DCount("*", "[tblPeople]", "[LastName]= '" & Me.Lastname & "' And [PODate] = #" & Me.PODate & "#") Then
......
And it works well.
In case an existing record is found, I want to jump to it.
I tried using the following code, but it gives a syntax error
Me.RecordsetClone.FindFirst "[Lastname] = '" & Me![LastName] & "' And [PODate] = #" & Me.PoDate & "#"
Me.Bookmark = Me.RecordsetClone.Bookmark
Can't figure out what is wrong here.
Any help will be appreciated.
View 4 Replies
View Related
Mar 9, 2005
can someone help me here? i'm trying to get a few selected employee from two tables, namely EmployeeParticulars and ProjectAllocation. EmployeeParticulars contained all the employees in the company whereas ProjectAllocation has two fields (PID - the project and EmpID - the employee who is allocated). One project consist of many employees.
Ok, let's move on to my problem now. I'm trying to get those employees who are not involved in a current project. i tried this
Code: Select Emp.Name from EmployeeParticulars Emp, ProjectAllocation Pro where Emp.EmpID <> Pro.EmpID
but it doesnt work. instead in returns me all the employees in the EmployeeParticulars table and on top of that, each employee appears 21 times!(this 21 i believe is from the 21 employees for this particular project) So, it actually runs 21*27(no of employees) = 546 times!
i was supposed to use != but i realise it doesnt not work in MS Access so i used this <> instead. however it does not give me what i want. Can someone assist me please.
I hope I've made my question comprehensive.
View 3 Replies
View Related
Mar 19, 2005
insert into date (ReqNum, date) values( 1 , '2005-03-20' )
I'm getting a syntax error with that. Does anyone know why?
View 1 Replies
View Related
Jul 6, 2005
Figured it out thanks.
View 1 Replies
View Related
