if checkbox true set other field to null


I have a checkbox and a date field. I want an expression (after update on the checkbox I guess) where if the checkbox is true then the date field should be set to null.

Something like
If [checkbox] = true then [response_date] = null
I'm still trying to learn the syntax. Any help much appreciated

Post your answer or comment

comments powered by Disqus
Hi guys,

I have a report with a subreport...

One of the fields in the subreport is a boolean field called 'Printed'

I've tried setting this field to 'True' in the OnPrint and On Format events of the detail section (which is where the 'Printed' field is placed) but I keep getting an Error 2448 message aka 'You can't assign a value to this object'...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me!Printed = True
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me!Printed = True
End Sub

How can I set a field in the subreport to true after it is printed?

Many thanks in advance


I'm having one of those days when I can't get my head around a problem.

I have an Access bound form, SQL back end. I'm trying to set a bound date text field to null (from its current date value), but it keeps yelling at me that "you tried to assign the null value to a variable that is not a variant data type". I've tried setting it to null and "".
When I set the field to vbnull, it sets it to 31/12/1899. Not good.

The answer is probably staring me in the face, but can someone please advise.

I have a row of fields (call them 1-4) set up and have a field (with label) below (5) it that is not visible. I want field 5 to be visible if any of 1-4 are not null but I want it to go back to being non-visible if all of 1-4 are null.

So far I've tried many ways of addressing this with the latest being:

Private Sub IiF()
IiF OtherDeliveriestoUNK.Value = Null
IiF OtherAlternatePort.Value = Null
IiF OtherNameofAltPort.Value = Null
IiF OtherPreferUNK.Value = Null
ListOtherSpeciesUNK.Visible = False
Label445.Visible = False
ListOtherSpeciesUNK.Visible = True
Label445.Visible = True
End If
End If
End If
End If
End Sub

I'm relatively new to Access and would really appreciate any help you could provide.

Hi all

Is there a way to make a textbox (or other control) set to null from visual basic?

At the moment when I want to delete a value from a field, I have to code like this:

	txtTextBox.value = ""

and then when I want to check for null value I have to do it like this:

	if txtTextBox.value  "" or Not IsNull(txtTextBox.value) then

I find this very messy coding, and hard to make the coding to work properly.

Is there a better way of doing it like this?

Thanks in advance

I found one other similar topic but it did not help and still remains unsolved. I also apologize that i had to submit this from my phone but here we go.

The Scenario: i have three columns labeled DonationValue, NetDonation, and PickupRequired. Both donations are text based and pickup is a yes/no checkbox. I know i must edit this information in the field box in design view for the query. I know this as the query it was required that i copy it from has NetDonation: [DonationValue]-8.75 as its field value.

The Request: It has been asked that if the checkbox is marked true I need to take the amount thats in DonationValues and subtract 8.75 from it to create new NetDonation. At the same time if the box is false NetDonations = to DonationValue.

I am stumped. We just learned about crosstabs and IsNull but i fail to see where that would help here. We learned about the build tab a chapter ago but i dont know where to begin. If you need clarification please ask. Again, posting from my phone and i apologize.

Hi all

I have a form which displays multiple items. I have a checkbox to indicate whether the item (an order) has been invoiced. If this is selected I want two other fields to be updated, the user and the date/time.

I'd like this to happen on the form instantly if possible into fields the user can't edit, or in the background (set as not visible).

I've had a go at the below code and entered it in to the afterupdate for the checkbox. It doesn't work, hence why I'm now here! The fields are tblWIPcomments_billed, which is the checkbox, tblWIPcomments_billedon which is the date field and tblWIPcomments_billedby which is the user field. I also have a procedure called getuser which finds and stores the current user.

Private Sub tblWIPcomments_billed_AfterUpdate()
If (Me.tblWIPcomments_billed) = "yes" Then
Me.tblWIPcomments_billedon = Now()
End If
End Sub

In my Access Database in Table1, I have one record that looks something like this. (eg. "fldProd" represents field name, while "Apple" represents field value.)
fldProd - Apple (text value)
fldPIO - CF (text value)
fldFam - 1 (text value)
fldSer - 2 (text value)
fldTra - 3 (text value)
fldInt - LZ (text value)
fildQty - 5 (integer value)

The MasterTable looks like this.
fldProd - Apple
fldDesc - 3D All
fldPio - CF
fldFam - 1
fldSer - null
fldTra - 3
fldInt - null

So essentially I would need a VBA module the would match up the MasterTable fields from a record with corresponding fields in the record from Table1. Notice, "fldDesc" in MasterTable does not exist in Table1, so it wouldn't be apart of the code's matching process. For each record it will only attempt to match "fldProd","fldPio","fldFam","fldSer","fldTra","fld Int". So "fldQty" in Table1 would remain untouched as well.

Then the module will need to nullify fields in Table1 that appeared "null" in the MasterTable. So after the VBA module was executed, the record in Table1 would look like this.

fldProd - Apple (text value)
fldPIO - CF (text value)
fldFam - 1 (text value)
fldSer - null
fldTra - 3 (text value)
fldInt - null
fildQty - 5 (integer value)

Any of you experts have any ideas? I'm not a code writer, so I'm totally stuck.

I attached an excel file that illustrates what I'm trying to accomplish for your reference. Thanks for any help!!! Attached Files Example1.zip (14.9 KB, 3 views) Reply With Quote 03-05-2010, 11:15 PM #2 ajetrumpet Banned Windows Vista Access 2007 Join Date Mar 2010 Location N/A Posts 2,698 Originally Posted by KP_SoCal So essentially I would need a VBA module the would match up the MasterTable fields from a record with corresponding fields in the record from Table1. I think the one thing you are missing here is the coverage of all the "what if's" in a situation like this. Before you run the code I've written, make a new field in table1, call it "fldupdated" and make it a BOOLEAN type. make the default value "false" in table design view.

after the new field, here's the solution to use if you read it the way I read it: Code:
function kp()

dim ctr as integer
dim updaterec as boolean
dim db as dao.database
dim rs as dao.recordset
dim rs2 as dao.recordset

set db = currentdb
set rs = db.openrecordset("SELECT " & _
                          "fldProd, fldPio, fldFam, fldSer, fldTra, fldInt, fldupdated " & _
                          "FROM table1", dbopendynaset)
set rs2 = db.openrecordset("SELECT " & _
                           "fldProd, fldPio, fldFam, fldSer, fldTra, fldInt " & _
                           "FROM mastertable", dbopendynaset)


with rs2

   do until .eof
      do until rs.eof

      if rs!fldupdated = 0 then

         updaterec = true

            for ctr = 0 to 5
               if not isnull(.fields(ctr)) then
                  if .fields(ctr)  rs.fields(ctr) then
                     updaterec = false
                        exit for
                  end if
               end if
            next ctr

         if updaterec = true then
               rs!fldprod = iif(isnull(!fldprod), null, !fldprod)
               rs!fldPio = iif(isnull(!fldPio), null, !fldPio)
               rs!fldFam = iif(isnull(!fldFam), null, !fldFam)
               rs!fldSer = iif(isnull(!fldSer), null, !fldSer)
               rs!fldTra = iif(isnull(!fldTra), null, !fldTra)
               rs!fldInt = iif(isnull(!fldInt), null, !fldInt)
               rs!fldupdated = -1
         end if

      end if


end with


set db = nothing
set rs = nothing
set rs2 = nothing

end function 
This of course says that more than one rec in table1 can be updated from the same record in mastertable. But then again, that's how I read it...

Greetings Gurus, long time no see...

I was wondering if the following was even possible because after much googling I can't seem to find an answer.

Basically I have a sub-form (lets call it frm2) which looks up and displays fields based on the "ID" field in another subform (called frmIn).

What I would like to do is find a way so that the various textboxes in frm2 turn blank once the last record in the frmIn has been actioned (ie there are no records in the main form).

I tried

	If (IsNull([Forms]![entry]![frmIn]![ID])) Then
'Me.RecordSource = ""

but that sets most fields as #Name and resets frm2s recordsource.

What I am looking to do is something like

	Dim AllRecords as ???
If (IsNull([Forms]![entry]![frmIn]![ID])) Then AllRecords = Nothing/Null

Suggestions welcome.

Thank you.

I have a form that allows the user to click on objects. When a given object is selected I store the selected object label name in a table. The label that is stored is used to filter the next form that is opened to that particular value. This all works fine but after I view that record, I would like to set the Label to NULL so I can view other records on the form if need be.
Thanks in advance - John

I want to add a textbox to my forms that either says "Yes" or "No" depending on what a non-visible checkbox is. My forms look funny with checkboxes all over the place (Yes or No fields). My reports would look better with "Yes" and "No" rather than checkboxes.

The question is, the control source of the new unbounded textboxes (or anything else for the matter), I don't know how to write the expression for it.

Can I just say...

if checkbox1=true then

I understand how to write these in the VBA builder. But in the expression builder...

I have a form where the user selects check boxes to choose what fields to include in a report.
Because of the massive amount of data, I need to send it to a temp table and then on to Excel and not use a query.
I have this line of code, for the insert into temptables for other forms that don't require the yes/no box and it works very well, but I can't figure out how to do it with these yes/no conditions.

This is the execute line that inserts into the TempTable for the other forms:
db.Execute "INSERT INTO TempPicktbl (Field1, Field2, Field3, Field4, Field5)" & strQuery, dbFailOnError

How would I write: If Check1=True Then INSERT INTO TempPicktbl Field1
If Check 2=True Then INSERT INTO TempPicktbl Field2
If Check 3=True Then INSERT INTO TempPicktbl Field 3

I'm pretty sure it's the INSERT level where I need to put this code.

Hello everyone.
In my database, I've got a journal table, in which I have a field: Hour
Thing is, I would like my customer to be able to type hours AND I will be able to make a query that will pop up a msb box IF the current time is earlier then the Hour field.
Problem is, when I put "Date/Time" field type, and set the format to short, it still shows the date picker, and let me choose date but not hour.

How can I change that field so I can type in it an hour like 13:30, but to be also able to make a query on it?

Thanks in advance.

Im not sure if this is the right area to post this but what i wanted to know is how i would set the field "status" to OutStanding when a record is added into the table.

If half the fields in each record need to be updated to null and not all the fields will necessarily be filled in, what is the best way to do it?

I have a field within a form, which I want all the users of the database to see, but I only want it to be editable if your windows logon name is one of the following:


I know I can use the environ variable - environ ("username") and I think I should set this code under the properties of this field possibly the on click setting. Can anyone help?

The reason why I want to do it by Windows logon as this form is widely used by many people and there is no logging into the form at startup. I currently have set the changing of this field to be recorded in a historical view, But I still need to restrict the entry to this field.

I'm having a bit of a block as I know I've done this before.

I want to be able to open a form with certain fields hidden based on the user's selection. I have a form with a combo box that has several values from which to chose.

If the user selects value a, code would read:

If Me.search = "By Vendor" Then
DoCmd.OpenForm "frm_search", acNormal, , "[Forms]![frm_search].[exterior].visible = false"
End If

But I can't remember where to reference setting the field to false.

I know this is easy and sorry if it's a waste of time.


i can't find the bug in this coding.
can't someone help me?
note: i have a table "tblCustDetails" and a form "FrmCustomerDetails"
I can't move on without finding the bug, it might occur in my other forms

Code: Option Compare Database Option Explicit 'Declaring Variables to be used Dim dbsCustomer As DAO.Database Dim rstCustomer As DAO.Recordset Dim PresenceCheck As Boolean '-------------------------------------------------------- 'Purpose: to load Customer Details form with Customers's first record. 'Event : Form Load '-------------------------------------------------------- Private Sub Form_Load() 'Executing database & recordset Set dbsCustomer = CurrentDb Set rstCustomer = dbsCustomer.OpenRecordset("tblCustDetails", dbOpenDynaset) 'To disable CustomerId textbox Me.txtcustomerid = True 'set Search Client combox to nothing Me.cmbSearchCustomer = "" 'Cycle to first record and display record rstCustomer.MoveFirst 'Calling procedure to display Customer Record Call displayRecord End Sub '----------------------------------------------- 'Purpose: Displaying record while updating search box 'Event : lookup value for combo box after update '----------------------------------------------- Private Sub cmbSearchCustomer_AfterUpdate() With cmbSearchCustomer 'Pushing data to form from table Me.txtcustomerid = .Column(0) Me.txtsurname = .Column(1) Me.txtname = .Column(2) Me.txtaddress = .Column(3) Me.txtrentalid = .Column(4) Me.txtTelephone = .Column(5) Me.txtMobile = .Column(6) Me.txtoverdue = .Column(7) Me.txtfine = .Column(8) End With End Sub Private Sub cmbSearchCustomer_LostFocus() 'Set Search Combo Box to nothing Me.cmbSearchCustomer = "" End Sub '--------------------------------- 'Purpose: Cycle to previous record 'Event: On click previous button '--------------------------------- Private Sub btnPrev_Click() 'Cycle to previous record rstCustomer.MovePrevious 'Display message if current record is at beginning of file If rstCustomer.BOF Then rstCustomer.MoveFirst 'Remain at the first record MsgBox "Cannot Cycle backwards. Already at the first record!", vbInformation + vbOKOnly, "Calliope Video store" End If Call displayRecord 'Display the data from Customer table End Sub '------------------------------ 'Purpose: Cycle to next record 'Event: On click next record '------------------------------ Private Sub btnNext_Click() 'Cycle to next record rstCustomer.MoveNext 'Display message if current record is at end of file If rstCustomer.EOF Then rstCustomer.MoveLast MsgBox "Cannot cycle forward. Already at the last record!", vbInformation + vbOKOnly, "Calliope Video Store""" End If Call displayRecord 'Display the data from Customer table End Sub '------------------------------ 'Purpose: Add new record 'Event : On click add new record '------------------------------ Private Sub btnNewRecord_Click() 'Set all fields to null Call clearForm 'Enable the CustomerId text box Me.txtcustomerid.Enabled = True 'set focus to CustomerId text box Me.txtcustomerid.SetFocus End Sub '------------------------------------------- 'Purpose: Delete current record 'Event : On click delete button '------------------------------------------- Private Sub btnDeleteRecord_Click() 'Using local Variable Dim DisplayMsg As String 'Confirmation message DisplayMsg = MsgBox("Proceed with deletion of this record?", vbYesNo + vbQuestion, "Calliope Video Store") 'If input is yes If DisplayMsg = vbYes Then 'Delete current record With rstCustomer .Delete 'delete current record MsgBox "Current record deleted!", vbInformation + vbOKOnly, "Calliope Video Store" .MoveNext 'Cycle to next record and display If .EOF Then 'if at end of table .MoveLast 'Stay to last record to avoid error Call displayRecord 'display data into form End If Call displayRecord 'display data into form End With End If End Sub '------------------------------------- 'Purpose: to save new record 'Event Procedure: Save button on click '------------------------------------- Private Sub btnSaveRecord_Click() PresenceChck = False 'Check if fields are empty by calling checkEmpty procedure Call checkEmpty 'check if fields are empty If PresenceCheck = True Then 'error message MsgBox "Record cannot be saved as it contains blank fields!", vbExclamation + vbOKOnly, "XYZ Co. Ltd" Exit Sub Else 'Save record rstClient.MoveLast 'Move to the last record rstClient.AddNew 'add a new record Call saveRecord 'Transfer the data from form to table rstClient.Update 'update the table MsgBox "The record has been saved successfully!", vbOKOnly + vbInformation, " XYZ Co. Ltd " End If End Sub '------------------------------------------ 'Purpose: to exit Client Form 'Event: On click, open Main Menu '------------------------------------------ Private Sub btnMainMenu_Click() 'Close Customer Details Form dbsClient.Close 'Close Customer Details record set rstCustomer.Close 'Close Customer Form DoCmd.Close 'Open Main Menu Form DoCmd.OpenForm "frmMainMenu" End Sub '--------------------------------------------------------------- 'Purpose: to display customer records from tblcustomer to Customer Form 'Event: User Defined prcedure '---------------------------------------------------------------- Private Sub displayRecord() 'Display the data from table With rstCustomer Me.txtcustomerid = .Fields!CustID Me.txtsurname = .Fields!CustSurname Me.txtname = .Fields!CustName Me.txtrentalid = .Fields!RentalID Me.txtaddress = .Fields!Address Me.txttelephoneno = .Fields!TelephoneNo Me.txtmobileno = .Fields!MobileNo Me.txtoverdue = .Fields!Overdue Me.txtfine = .Fields!Fine End With End Sub Private Sub clearForm() 'Clears all the text boxes in the form Me.txtcustomerid = "" Me.txtsurname = "" Me.txtname = "" Me.txtrentalid = "" Me.txtaddress = "" Me.txttelephoneno = "" Me.txtmobileno = "" Me.txtoverdue = "" Me.txtfine = "" Me.cmbSearchCustomer = "" End Sub '---------------------------------------------------- 'Purpose: TO save all data from the form to the table 'Event: User Defined procedure '---------------------------------------------------- Private Sub saveRecord() 'Save all data from the form into the table With rstCustomer .Fields!CustomerId = Me.txtcustomerid .Fields!Surname = Me.txtsurname .Fields!Name = Me.txtname .Fields!RentalID = Me.txtrentalid .Fields!Address = Me.txtaddress .Fields!TelephoneNo = Me.txttelephoneno .Fields!MobileNo = Me.txtmobileno .Fields!Overdue = Me.txtoverdue .Field!Fine = Me.txtfine End With End Sub '--------------------------------------------------- 'Purpose: Presence check done on empty fields 'Event: User Defined procedure '--------------------------------------------------- Private Sub checkEmpty() 'Customer ID If Me.txtcustomerid = "" Then MsgBox "Please, enter a Customer Code!", vbOKOnly + vbExclamation, "Calliope Video Store" PresenceCheck = True Exit Sub End If 'Surname If Me.txtsurname = "" Then MsgBox "Please enter Customer's Surname!", vbOKOnly + vbExclamation, "Calliope Video Store" PresenceCheck = True Exit Sub End If 'Name If Me.txtname = "" Then MsgBox "Please enter Customer's Firstname", vbOKOnly + vbExclamation, "Calliope Video Store" PresenceCheck = True Exit Sub End If 'Rental ID If Me.txtrentalid = "" Then MsgBox "Please enter Rental ID", vbOKOnly + vbExclamation, "Calliope Video Store" PresenceCheck = True Exit Sub End If 'Address If Me.txtaddress = "" Then MsgBox "Please enter Customer's Address", vbOKOnly + vbExclamation, "XYZ Co. Ltd" PresenceCheck = True Exit Sub End If 'Telephone Number If Me.txttelephoneno = "" Then MsgBox "Please enter Customer's Telephone Number", vbOKOnly + vbExclamation, "XYZ Co. Ltd" PresenceCheck = True Exit Sub End If 'Mobile Number If Me.txtmobileno = "" Then MsgBox "Please enter Customer's Mobile Number", vbOKOnly + vbExclamation, "XYZ Co. Ltd" PresenceCheck = True Exit Sub End If 'Overdue If Me.txtoverdue = "" Then MsgBox "Please state current status of customer's rental", vbOKOnly + vbExclamation, "XYZ Co. Ltd" PresenceCheck = True Exit Sub End If 'Fine If txtoverdue "" Then If Me.txtfine = "" Then MsgBox "Please state the amount of fine the customer has to pay if ", vbOKOnly + vbExclamation, "XYZ Co. Ltd" PresenceCheck = True Exit Sub End If End If End Sub

Hi all,

I'm trying to group records by year. Tried typing in the following in Design View:

Year: DatePart("yyyy",[SurveyDate]) into the Field row in Design view. It yields #Error all the way down.

Then I realized my SurveyDate field was a "Text" field, not Date/Time. Figured that's probably the problem. So tried to convert it to Date/Time and got the following error:

---"Microsoft Access can't change the data type. There isn't enough disk space or memory."

Did a search on this forum and found that this is a common problem with big databases (mine's 534,000 records). So I tried to create a new table with all the same fields -- but with SurveyDate as a Date/Time type, and append all the data from the old table into it.

I'm getting the following error:
---"Microsoft Office can't append all the records in the append query.
Microsoft Office Access set 534582 fields to Null due to a type conversion failure"

Any ideas? I wasn't sure how to do the other option suggested in that thread: creating a make table query and using the str() and lng() function to change the data type. [Here's a link to that thread: http://bit.ly/qvzdN7]

Help, o brilliant Access wizards!

In the report I am creating I would like to set the Blank or Null fields for a text box to display "N/A". I cannot seem to find some sort of a default setting and I would not like to do this in the Form for certain reasons. Thanks

Is there a way that you can set a field dependent on another? I have 2 fields, Problem Description and Problem Type. The Problem Description field is a text box and the Problem Type is a drop down box. If data is entered in the Problem Description field, how do I require users to also select from the drop down box in the Problem Type field? Thanks for your assistance.

I have an after update event that sets a field to value [Query]![Field]

Access is giving me the error that the "Identifier [Query]![Field] could not be found". Any ideas on what is going on or how I should proceed?

Hi all

i would like to know if there is a way to return a field to null

thanks in advance


I've been using this forum for a couple of weeks but this is my first post, so apologies if I've put it in the wrong place!! I have a table with some drop-down lists to fill certain fields, and I would the available list options to change based on information in other fields. To provide an example:

Field One: The drop down list lets the user choose "Alphabet" or "Number"

Field Two: The drop down list lets the user choose A-Z if "Alphabet" is selected in Field One or 1-100 if "Number" is selected in Field One

Hope I've been clear enough. Any help is greatly appreciated!!



Is there a way to enter blank fields into a maketable query without using Null, MfstAccsAmt: Null? I can't just use design view to change the field type because the table is deleted everytime the code is run. I have an update query that runs after the maketable query and populates the blank fields in the maketable. One of the fields the update query pulls from is a currency type. This is type is not carried over I am assuming because the field is set to Null. But if I do not set the field to Null it automatically creates an expression, Expr2: [MfstAccsAmtl]. Any suggestions?

Not finding an answer? Try a Google search.