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
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.
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
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.
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!
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.
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
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???
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.
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
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.
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.
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.
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
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
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..
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
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
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?
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);
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.
