I have a SQL Server 2005 backend table with a varchar(1) field which allows nulls. The field is populated from a combo box in Access 2012. The field can be set to 1,2 or 3 via the combo box or left blank in which case its value will be null. Sometimes I need to reset this field to null. How can I do this via a combo box? I know I could just set this to an empty string but this would mean re-writing a load of queries which a looking for a number or null.
Something like 1;"First";2;"Second";3;"Third";Null;"Blank"
I have a combo box, 'Type' that has two values - "Instrument" and "Equipment".Another combo box control, 'Criticality', has an Enabled = False value set OnCurrent.
When the user selects "Equipment", Criticality goes Enabled = True and when OnUpdate to "Instrument", Criticality goes Enabled = False.The Criticality field correctly displays and switches off if someone selects Instrument.
However, if someone puts a value in whilst Type is set to "Equipment" and then Type is reset to "Instrument" and in the meantime the user has selected a value for Criticality, the value stays 'stuck' in the Criticality box, despite the field being disabled. It is a bound field.How do I purge any contents stored in Criticality when the field is disabled? I presume it's something like an SQL statement to set the value to null, but I'm not quite certain.
On my unbound form I have a combo box with a record source type set to value list and the record source with two choices in it. I would like the combo box to reset back to either blank or a the phrase "select one" once a record is saved. If it shows "select one" I do not want the user to be able to actually select that. As I said the form is an unbound form and I have used the coding of "insert into" to save the new record to a table. I have been able to clear the text box on this form when the new record is save by resetting it's value to "" but have been unable to reset the combo box to blank.
how do I reset the combo box through code after a save to display nothing - or is there a way to add a non-selectable choice to the list and have it display when the form is refreshed.
We just upgraded to Office 2010 from 2003 and I am now getting 'invalid use of null' after choosing a valid combo box option. When I debug, the line that highlights has to do with a text box on the form rather than the combo box. Here's the code highlighting:
frmLines = Me.WorkLoadUnit
This is the code in it's entirety.
Private Sub fsubProductivityInput_Calculations() On Error GoTo err 'Calculate Breaks and Net Hours Dim frmLines As Double Dim frmHours As Double Dim frmLPH As Double
I have a combo box in a form which simply pulls a text field from a single table - approximately 100 values to choose from. On the After Update Event, there is a basic "If null this, else that" VBA which just hides a subform if the value is null. This works fine, except there are a handful of values (I've found at least 6) that when chosen, trigger the NULL portion of the code! All the values are very similar, so I don't know what the problem is - for example, the value "D-11" works, but neither "D-10" nor "D-12" does.
I have a form, form1. Inside there is a combo box, cbo1, that gets its values from table1.On form load, I want an expression that checks if table1 has any values to "send" to cbo1. If yes, meaning, if after form loads cbo1 is loaded with table1's values, I want cbo1 colored RED, if not, colored WHITE.
I am assigning a recordset using vbs code. The variable "CB_proj" is a combobox value. The problem comes when the combobox is null, the program errors out and even if I try to exit the routine with an if statement, it still compiles the line of code and errors out because the object doesn't exist. how I can get it to not compile that error if my combo box value is null?
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
I have an address box that I would like to auto populate with a double click event. Basically, if the location address is the same as the main company address, and there isn't any data already in the location field, then I would like the user to be able to just double click the location address and the field gets automatically populated with the main company address.
Private Sub Address_DblClick(Cancel As Integer) If IsNull(Me.Address) Then Me!Address = Me.CompanyName.Column(1) End If
So this works fine if the main company address actually has an address in it. But if it doesn't then double clicking the location address produces a run time error 3315 for trying to populate a null value.
If (DataErr = 2113) Then Select Case Screen.ActiveControl.Name Case "DA_Start" MsgBox "invalid date", vbExclamation Me.DA_Start. = Null End Select Response = acDataErrContinue End If End Sub
This will pop up the error message, but will trigger the debuger when gettin to "Me.DA_Start = Null"
I have a form with a subform. The subform is used to report a sales total, for today and for the month. When there are no sales today (a null result in one of the fields) the entire subform goes blank.
How can I make it report a zero in the field instead of blanking the entire subform?
I am having problems with setting up a set of combo boxes.
What I am trying to do is if combo Productline is empty then in combo PartNumber would show all products but if combo Productline has a value selected then in the combo partnumber would only be able to select the partnumbers in that productline.
I have a form called PAF_Assignment and on this form there are many textboxes for editing. After all fields are entered, the user clicks on a button and this button populates the PAF_Issued_Date field with the actual date.
Then I want to disable (enable=False) all fields on this form once there is a date on PAF_Issued_Date as I don't want any user to make any changes after submitting it, although the user would still be able to read the information submitted.
I have tried the following...
PAF_Assignment Form AfterUpdate Event If Me.PAF_Issued_Date=Not null Then Me.FieldName1.Enabled=False Me.FieldName2.Enabled=False Me.FieldName3.Enabled=False
[Code] ......
However this is not working, there is no error message or anything but the fields remain enabled with the date...
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click() On Error GoTo Err_Assign_Classes_Click Dim stDocName As String Dim msgboxstring As String Dim stLinkCriteria As String Me.name_found.Requery
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
I have a multi-select listbox (called PotentialItems) which behaves perfectly unless one of the fields has a null value in it, in which case I get an error 13, "Type mismatch" in the following code. It is valid business-wise for column 9 to be null.
Code: Dim varItem As Variant Dim dblTargetPrice As Double With Me.PotentialItems For Each varItem In .ItemsSelected If Not IsNull(varItem) Then dblTargetPrice = Nz(.Column(9, varItem), 0) '<----- Error here End If Next End With
I have a subform with continuous records. One of the fields in the recordset of the subform is a field named "Remarks". This field does not need to be visible on the continuous subform as it is rare that this field will have any entries.
I plan to apply conditional formatting on another field (IDcardNo) in the record line of the continuous subform so that when field "Remarks" contains any data it will show as a different format on the field IDcardNo.
I would like to make a small form appear when one points to the IDcardNo field with the different format, so that the data in the field "Remarks" pops up when one points to the field IDcardNo with altered format, showing that there is data in the field "Remarks".
The problem that I have is that when I add a new field to my form at runtime I get the following error Runtime Error '2424' The expression you entered has a field, control, or property that Microsoft Access can't find.
This error appears when I try to update any field. I posted this before and was informed that access has a field counter limit up to 750 on a form, this includes fields which were deleted or hidden. I dont deliberately add and remove fields, but this is the main form in my database so it is not unbelieveable that this may be at the limit and this is why the error is occurring. Does anyone know how to reset this?, or what I should do to get around this.
I have a table which will be completely emptied and refilled. The table has a field autonumbered it is also the primary key. When the table is refilled I want this field to start from 1. How can I do that. Yes some people will say this subject has been discussed search for it. But here the issue is somewhat different we have an existing field and I do not want to compact the database each time the procedure runs.
I have a form based on a table with just sales quotes and dates. I am using the salesquote as a combo box to select corresponding records in a subform.
Code: Private Sub cboSalesquote_AfterUpdate() Me.Filter = "[salesquote]=" & cboSalesquote Me.FilterOn = True End Sub
Works fine. But not the mgr says his users get confused when they are making changes in the subform which record they are changing so they want when they select a record from the combo box and the records appear in the subform; they want the combo box to go blank. I am not sure how to do this because if I reset the combo box to go blank; the subform records will go blank as well.
I currently have a continuous form in my database that has 24 records in it. every record is blank except for a time (I will post a screenshot)
Our employees are instructed to enter various information every hour in this form. coding a button that, when clicked, will create 24 new records in the table that this form is based on, insert times in those 24 records, and then display those 24 records in the form. It would have the illusion that the end user it "resetting" the form at the end of his shift and preparing a clean form for the next person.
In the spreadsheet that we currently use, there is a button that, when pressed, saves the workbook with the date in its file name and then clears all of the end-user's data so that the next shift has a clean spreadsheet to use. I would like to mimic this button as closely as possible in MS Access
I understand that this database design is not exactly ideal in the world of database engineering; however, I was told to make this a perfectly seamless transition from the excel spreadsheet we currently use (there is a folder with 900+ workbooks from over the years. This obviously makes the information useless - we need all of the information in one file - this database)
I am creating a program to allows us to figure out where our personnel are. I am using a continuous Form that shows the individuals name, phone numbers etc. and infront of that is a checkbox, and a combobox that shows "Leave", "TDY", "Contacted" etc. I also have two fields that fill up with what time they were contacted or when they reported in. The default status is "Not Attempted and the checkbox is not checked, and the time fields are blank.
I attempted to use this code:
Dim ctl As Control For Each ctl In Me.Controls Select Case ctl.ControlType Case acCheckBox ctl.Value = False Case acComboBox ctl.Value = "Not Attempted" End Select Next ctl
but it will only clear the one line (it doesn't clear the times yet, i figure i'd add that once I figure out to clear one). This seems to work if this was a single form, but not for continuous. How can I convert this for continuous forms?
I have a login form that closes automatically after a specified time if you do not login with user name and password. It also keeps track of how many times you login and whatever value you set for a password reset it forces you to change when that value is met.
Problem: If you do not login within the allotted time even though a password change has been scheduled, my code resets the login count back to 0.
This is what i want to get around if I can. Either I have to pause the timer or stop the reset.
After you enter the password is when the login count is fired.
And also when it checks if a new password is required.