I want to stop users from saving records within Access database without making the fields required in the table definition. Therefore I am putting edit code in the form's BeforeUpdate event. The following works great for text boxes but does not work for list boxes. Can anyone suggest how I can do this for list boxes or both list and text boxes (N.B. I have already tried replacing acTextBox with acListBox and also tried replacing acTextBox with acTextBox or acListBox)...
Using the following, paste this into a code module: - Public Function RequiredData(ByVal TheForm As Form) As Boolean
-
-
'Check that all TextBox controls have required data entered
-
-
Dim Ctl As Control
-
Dim Num As Integer
-
-
On Error GoTo Err_RequiredData
-
-
RequiredData = False
-
Num = 0
-
For Each Ctl In TheForm
-
If Ctl.ControlType = acTextBox Then
-
If Ctl = "" Or IsNull(Ctl) Then
-
Num = 1
-
Exit For
-
End If
-
End If
-
Next Ctl
-
If Num = 1 Then
-
MsgBox "Data is required in " & Ctl.Name & "," & vbCr & _
-
"please ensure this is entered.", _
-
vbInformation, "Required Data..."
-
RequiredData = True
-
Else
-
RequiredData = False
-
End If
-
-
Exit_RequiredData:
-
-
On Error Resume Next
-
If Not (Ctl Is Nothing) Then
-
Set Ctl = Nothing
-
End If
-
Exit Function
-
-
Err_RequiredData:
-
-
Select Case Err
-
Case 0
-
Resume Next
-
Case Else
-
MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, _
-
vbInformation
-
End Select
-
-
End Function
To call the code, you can use the following in the Before Update event of the Form: - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
If RequiredData(Me) Then Cancel = -1
-
End Sub
2 2163
Here is the general idea: - Dim ctl As Control
-
-
For Each ctl In Me.Controls
-
If ctl.ControlType = acListBox Or ctl.ControlType = acTextBox Then
-
If IsNull(ctl) Then
-
MsgBox ctl.Name & " contains no Value"
-
End If
-
End If
Next
NeoPa 32,556
Expert Mod 16PB
ADezii is right (of course), but you might want to consider simplifying your code somewhat. I don't believe that Controls can store empty string values (""), although there are scenarios where Fields can if they're set up to allow it. - 'Check that all TextBox controls have required data entered
-
Public Function RequiredData(ByRef TheForm As Form) As Boolean
-
Dim strMsg As String
-
Dim Ctl As Control
-
-
On Error GoTo Err_RequiredData
-
RequiredData = False
-
For Each Ctl In TheForm
-
With Ctl
-
If ((.ControlType = acTextBox) _
-
Or (.ControlType = acListBox)) Then
-
If IsNull(Ctl) Then
-
strMsg = Replace("Data is required in %N,%L" &
-
"please ensure this is entered.", %N, .Name)
-
strMsg = Replace(strMsg, %L, vbNewLine)
-
Call MsgBox(Prompt:=strMsg, _
-
Buttons:=vbInformation, _
-
Title:="Required Data...")
-
RequiredData = True
-
Exit For
-
End If
-
End If
-
End With
-
Next Ctl
-
Exit Function
-
-
Err_RequiredData:
-
strMsg = Replace("Error: %N%L%L%D", "%N", Err.Number)
-
strMsg = Replace(strMsg, "%D", Err.Description)
-
strMsg = Replace(strMsg, "%L", vbNewLine)
-
Call MsgBox(Prompt:=strMsg, Buttons:=vbInformation)
-
End Function
NB. The passing of TheForm is better done as ByRef as this doesn't require any recreating of the object.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jimmy Tran |
last post by:
Hi everyone,
I have a form consisting of a combo box and two listboxes, they get
their control source from a query. the whole form suppose to update a
table. when i run the form, i pick an item...
|
by: Janaka |
last post by:
Help! I have two ListBox controls on my web form. The first one gets
populated on entry with values from the DB. I then use JavaScript to copy
options from this ListBox to my second one. (I...
|
by: Mark |
last post by:
Hello.
I have a listbox whose rowsource is set to a saved query (call it
"qry_customer_list.") When I add customers to my database, I call the
listbox Requery method so that the listbox will...
|
by: doncee |
last post by:
I have a list box that is generated on a form by way of a
Parameter Query. Problem is whenever I try to refer to the list
box, i.e., to update the underlying table, I am getting a "null"
value...
|
by: whocracks |
last post by:
hi...
can you tel me how to insert null values in Access table using Vb code....
actually i want to store record having some fields with null values...plz....... i mean error is in query...
|
by: KMEscherich |
last post by:
Microsoft Access '97
Main Form and 3 subforms.
Code = VBA
Hi there am wondering how I would be able to do the following:
I need to ensure the end-user makes an entry in controls on both the...
|
by: Toby Gallier |
last post by:
Hello!
I have a form that is calculating averages as follows:
" =(NZ()+Nz()+Nz())/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to...
|
by: BurtonBach |
last post by:
I previously wrote the following query which gives me that data I wanted and currently returns 436 records:
SELECT qryTonnageSelect.Jobs_Job_ID, qryTonnageSelect.SumOfTransactions_TonsNetWeight,...
|
by: JFKJr |
last post by:
Hello everyone, the following Access VBA code opens an excel file and creates textboxes in a given range of cells dynamically. The code attaches "MouseUP" and "Exit" events to the textboxes (using...
|
by: =?Utf-8?B?R3JlZw==?= |
last post by:
I have the following three files.
1. Users.aspx is a webpage that uses the <asp:ObjectDataSourcecontrol to
populate a simple <asp:ListBoxcontrol.
2. The UserDetails.cs file creates a Namespace...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |