I use a form for changing data. So when the user selects a record from a listbox I fill all fields of the form with the content relating to the selected line in the box. Nearly everything runs as wished.
All updates take place immediately but the Boolean field does not update or better it updates only after I move the cursor over it. Then it is correct. To make it clear, I do not need to press a button or click, I only move the cursor over the boolean checkbox.
I access VBA after clicking the line in the listbox and use the dlookup command to get the right values. Do I need something like a requery for a boolean field ?
I'm trying to update a combobox(called TypeCombo) list values after I select a value in another Combobox(called manCombo). The values for both Combobox comes from the table called EquipmentTable. Which has 3 fields Manufacturer, Type and Model.
Code:
Private Sub ManCombo_AfterUpdate() Me.TypeCombo.Value = DLookup("[Type]", "EquipmentTable", "Manufacturer = " & Me.ManCombo.Value) End Sub
I know that the value I select in ManCombo is being passed into the DLookup but TypeCombo.Value always equals Null. Is this because the Dlookup returns multiple values and if so should the code be something like
Code:
Private Sub ManCombo_AfterUpdate() Me.TypeCombo.List = DLookup("[Type]", "EquipmentTable", "Manufacturer = " & Me.ManCombo.Value) End Sub
Here is the set up. I have a primary school database.
There is a main form that shows the students name and class.
The main form contains tabs for each subject (maths, english etc.). Each tab has a subform that is supposed to allow me to enter a comment about the student's progress.
My tables are set up properly (as far as I can tell).
The Master and Child (main form and subform) are linked using StudentID.
I use a query for fields in the Master form. Each subform has its own query that shows results for a particular subject.
The problem
My subform allows me to edit an existing comment fine (if I enter dummy data directly in a table).
BUT, if there isn't an existing comment, and I attempt to enter one, I get an error - "YOU CANNOT CHANGE A RECORD BECAUSE A RELATED RECORD IS REQUIRED IN TABLE tbl_Subjects."
What I have tried
I understand this error means I am violating referential integrity rules. But I can't see why. The tbl_Subjects is populated with 10 subjects and I am only trying to put a comment against an existing SubjectID.
I've checked that my Form Master / Subform Child is ok. I experimented with having StudentID _and_ Subject_ID to link the master and subform to see if that worked. It only made things worse because my subforms wouldn't display correctly.
I have a table in my DB named TblStock and another named TblStock2..I also have a form in the DB named "frmStock" with the control source being "TblStock" . This form has a sub form "FrmStock2Subform" with control source being "tblStock2". The two forms are lined by "PurchaseID". This form is made to be used in entering products that have been bought for replenishment of stock.The problem I have is that when I enter data in the main form, by the time i click on the subform to start entering data, the table "TblStock" gets already updated.
Now lets assume somebody was about to enter data in the subform and later on changes his mind when the "TblStock" had already been updated, this will cause a waste of useful space because there is no corresponding record in the table "TblStock2". Secondly, if many users are entering data into the DB using that same form, I guest there will be some problems too.What i want is that the form should update both tables when the users validates the records.
I have a subform with a textbox in the footer to count[id]. then on my main form i have another textbox that has a control source of =[subform].[form]![subformfootercontrol].
However when the subform updates the main form text is blank. typically i see a ?#Name or#Error if the control is pointing to the wrong place, but i have never see it stay blank.
Working in Access 2007 - I would like to have an updatable calendar in the header section of a form, and when this is changed by the user I would like the subforms (there are several) in the details section to be updated with various appointments with dates corresponding to the date selected in the header.
I'm working on a software that was developed by someone who left the company. The problem is that the software works for all locations execpt one that is in austria, after debuging I found out that there is a statement in the code that uses boolean true or false, the only way it worked is when I put an if statement with false or true as string, ex: if doc = "true" then buttom line has anyone faced this problem, I searched microsoft website and they said that some vb engines don't convert boolean to string. Please Please if someone has a clue try to help. Thanks.
I have a search tool, but right now it is 100% case sensitive in the sense that if i search for "test" and my entry is "Test" it will not return any results.
is there any way to setup so I could say search for "Test" and it would find "test" properly? Or even to the extreme where I searched for "Tes*" and it came back with the "test" entry.
I need to create a user interface that will allow a boolean type search of a field. This would mean item1 AND item2 AND NOT item 3, item1 or item2, item 1 NOT item2, etc.
I've set up a combo box setup with the following script, but I can't figure out how to adjust it to allow the multiples.
Please advise if I should adjust this or try a differenmt interface format for my users.
Option Compare Database 'Use database order for string comparisons Option Explicit
Private Function AfterCombo(WhichLine As Integer) Dim CBox As Control, TBox As Control, AndBox As Control, TBoxA As Control Set CBox = Me("Combo" & WhichLine) Set TBox = Me("Value" & WhichLine) Set AndBox = Me("And" & WhichLine) Set TBoxA = Me("Value" & WhichLine & "A") TBox = Null TBoxA = Null Select Case CBox Case "All", "Blank", "Not Blank" TBox.Visible = False AndBox.Visible = False TBoxA.Visible = False Case "Like", "Equal", "Less Than", "Greater Than", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "In List", "Not In List" TBox.Visible = True AndBox.Visible = False TBoxA.Visible = False Case "Between", "Not Between" TBox.Visible = True AndBox.Visible = True TBoxA.Visible = True End Select End Function
Private Sub Cancel_Click() DoCmd.Close End Sub
Private Function FormatList(ByVal List As String, FieldType As Integer) Dim NewList As String, CommaPos As Integer, Word As String NewList = "" Do While Len(List) > 0 CommaPos = InStr(List, ",") If CommaPos = 0 Then Word = Trim(List) List = "" Else Word = Trim(Left(List, CommaPos - 1)) List = Trim(Mid(List, CommaPos + 1)) End If If Word > "" Then Select Case FieldType Case DB_TEXT, DB_MEMO If InStr(Word, """") > 0 Then MsgBox "Don't type double-quotes in the list" End End If Word = """" & Word & """" Case DB_DATE If InStr(Word, "#") > 0 Then MsgBox "Don't type '#' in your dates" End End If If Not IsDate(Word) Then MsgBox "Your list contains non-date characters" End End If Word = "#" & Word & "#" Case Else If Not IsNumeric(Word) Then MsgBox "Your list contains non-numeric characters" End End If End Select NewList = NewList & "," & Word End If Loop NewList = Mid(NewList, 2) If NewList = "" Then MsgBox "Your list needs a valid value" End End If FormatList = NewList End Function
Private Function MakeNull(C As Control) If Len(Trim(C)) < 1 Then C = Null End Function
Private Function MakeSQL(WhichLine As Integer, FieldName As String, FieldType As Integer) As Variant Dim CBox As Variant, TBox As Variant, TBoxA As Variant Dim Condition As Variant, Delim1 As String, Delim2 As String CBox = Me("Combo" & WhichLine) TBox = Me("Value" & WhichLine) TBoxA = Me("Value" & WhichLine & "A") Select Case CBox Case "Like", "Equal", "Less Than", "Greater Than", "In", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "Not In" If IsNull(TBox) Then MsgBox "You have left a parameter blank for field [" & FieldName & "]" End End If Case "Between", "Not Between" If IsNull(TBox) Or IsNull(TBoxA) Then MsgBox "You have left a parameter blank for field [" & FieldName & "]" End End If End Select Select Case FieldType Case DB_TEXT, DB_MEMO Delim1 = """" Delim2 = """" If Not IsNull(TBox) Then TBox = QFix(TBox) If Not IsNull(TBoxA) Then TBoxA = QFix(TBoxA) Case DB_DATE Delim1 = "#" Delim2 = "#" Case Else Delim1 = "" Delim2 = "" End Select Select Case CBox Case "All" Condition = Null Case "Blank" Condition = " Is Null" Case "Not Blank" Condition = " Is Not Null" Case "Like" Condition = " Like """ & TBox & """" Case "Equal" Condition = "=" & Delim1 & TBox & Delim2 Case "Less Than" Condition = "<" & Delim1 & TBox & Delim2 Case "Greater Than" Condition = ">" & Delim1 & TBox & Delim2 Case "Not Like" Condition = " Not Like """ & TBox & """" Case "Not Equal" Condition = "<>" & Delim1 & TBox & Delim2 Case "Not Less Than" Condition = ">=" & Delim1 & TBox & Delim2 Case "Not Greater Than" Condition = "<=" & Delim1 & TBox & Delim2 Case "In List" Condition = " In(" & FormatList(TBox, FieldType) & ")" Case "Not In List" Condition = " Not In(" & FormatList(TBox, FieldType) & ")" Case "Between" Condition = " Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2 Case "Not Between" Condition = " Not Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2 End Select MakeSQL = " And [" + FieldName + "]" + Condition End Function
Private Sub OK_Click() Dim Where As String Const ObType = "Form" Where = Where & MakeSQL(1, "Lyrics", 10) Where = Where & MakeSQL(2, "TrackTitle", 10)
On Error GoTo OKCApplyError If Where <> "" Then Where = Mid(Where, 6) DoCmd.OpenForm "MasterFormQuery", , , Where Else DoCmd.OpenForm "MasterFormQuery" End If
Private Function QFix(ByVal X) Dim P As Integer If IsNull(X) Then QFix = Null Exit Function End If P = InStr(X, """") Do While P > 0 X = Left$(X, P) & """" & Mid$(X, P + 1) P = InStr(P + 2, X, """") Loop QFix = X End Function
Private Sub exitselectform_Click() On Error GoTo Err_exitselectform_Click
I downloaded Checkbox open form.zip from this site. It uses option group for the checkboxes
It runs perfectly when I double click in access but i would like to use this in vba when called from another form. When i put the code in and try to run it I get the error:
Compile Error Method or data member not found
It is referring to .Frame8
Here is the code I used:
DoCmd.OpenForm "Mainform", acNormal
Select Case Me.Frame8 Case 1 Open Form 1 Case 2 Open Form 2 Case 3 Open Form 3 Case Else MsgBox "You did not make a selection" End Select
my teacher asked me to do Access homework to change data type from Text to Boolean. Data in "Owned car" field show as Y or N 17178 so I click on Design View and change datatype of this field from "Text" to "Yes/No" , than when I saved it show a message 17179 When I back to Datasheet view, all data in "Owned car" field change to be "No".
How can I change data type to be Boolean type without deleting my original data??? I want them to show the same as Y or N.
I have designed an invoice for a project that shows the amount of money needed to be paid, some of this has already been paid and some hasnt, how am i able to make it so that the values that have been paid and therefore have been checked are not include in the query. please help.
I have an Access 2010 db which has a load of global variables of type Boolean. The values are different for each machine the database is stored on, so I have a linked table to a separate Access mdb. On startup the database opens the linked table and assigns values to each variable.
However I need to differentiate between a valuable value of False and the variable having not been set. As boolean variables default to False, this is a problem. One option is to use data type Variant, but I know this is not efficient. I am considering using data type Byte instead of Boolean, with 1 as False and 2 as True.
I have a continuous form that has several checkboxes in it. Only one is initially enabled and I want the first to impact if the others are enabled, but only in the currently selected record.
I tried VBA but quickly found that this enables/disables the checkboxes in ALL of the records. Next I found out about conditional formatting--which does exactly what I want, but only works on text boxes and combo lists.
Is there any way that conditional formatting can be used to impact (vs. be impacted by) a checkbox?
I have a subform in datasheet view. In the main form I have various combo boxes to filter the data in the subform. I want to be able to check off the checkbox in the subform then filter all records that are checked, however, I am not able to check any of the checkboxes when I'm in my main form.
Example: Sales Region, Order Date, are two fields
I can go in and select "north", "5/20/2013" then filter and let's say I get 55 records
So within the north region on 5/20/2013 I only want to select 5 of the 55 records by checking them off in the subform then in the main form I would drop down the unbound combo box to 'yes' and it would give me all records in the 'north' region with an order date of '5/20/2013' that I checked which should be 5 in this case. But, it won't let me check the box in the subform.
My tab name is SPA and it is the third tab in my form. I want to hide it if the AddToSPA checkbox is checked. This is what I have but I'm getting error 'Compile error: Method or data member not found"
Code:
Private Sub Form_Load() If Me.AddToSPA = True Then Me.SPA.Pages(2).Visible = True Else Me.SPA.Pages(2).Visible = False End If End Sub
1. In a form that I have created, I have a checkbox that I want to have checked when the form (ticket) that was filled out has been completed by our company. I want the checkbox to only be accessed by us. How do I achieve this to only certain users have access to check that box?
2. I have a main screen that the form is able to be accessed by. In that home screen I have a query that shows what tickets have not been completed (checked). How do I get those tickets that have been checked, to not show up on that screen?
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 am new to access forms. I am trying to use the 2 checkboxs for my make table query.
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.
Seems like a iif statement is not working in my query. I am trying to create an event now but I do not understand the VB codes.
I have a table, it is an on going update for a meter reading. When the last record for a particular meter is closed, a new record is created with updated information and carry forward numbers. Something like this:
Meter# - Opening - Closing - Active - Date 123 - 1776 - 1876 - No - 2014-Jul-04 123 - 1876 - 1976 - No - 2015-Jul-04 123 - 1976 - 0 - Yes - 2016-Jul-04
This is in a table form for the user to see the old data, and be able to edit the last line only. My problem is how to I tell access that that can edit only the last row? Since there are 1,000's of meters involved this is not going to be the last line of the datatable nor is it a new record. The trick is the use can edit only rows where the Active field is True. Once set to false the record should not be changed any more.
