I am fairly new to VBA & need a little help if anyone can spot my mistake in my code
need to check all text & combo boxes in form so that if all are empty then a message appears telling the user to fill it in but when it runs this does not happen (some more after this bit!):
Code as follows:
Private Sub CmdOpenSearchResults_Click()
On Error GoTo Err_CmdOpenSearchResults_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim ctl As Control
Dim fGotOne As Boolean 'Boolean is a true or false statment
Dim Msg, Style, Title, Response, MyString
fGotOne = False 'assumes wont find anything
For Each ctl In Me.Controls 'Controls is an object which has to be defined by a dim statement
'Debug.Print ctl.Name 'check is going around all controls - this prints in debug window in view, after adding next (line after debug line) save and open debug win then open frm normal and srch and look at debug window all controls listed
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If Not IsNull(ctl.Name) Then
fGotOne = True 'says found one
Exit For 'This says if is a text or combo box then if it is not empty then drop out to next stage - did you find anything?
If fGotOne = False Then
Msg = "A search cannot commence without at least one entry, please update one of the options and try searching again"
Style = vbOK + vbApplicationModal
Title = "Make a Selection"
Response = MsgBox(Msg, Style, Title)
The rest of the code runs a filter which should be the record source of a subform but I cannot workout how to tell the main form to open and while setting the subform to use the VB as a record source - what is the link in VB - I am getting an error to say can't find the form FrmSearchResultsub but I can't see a way to do this
Try putting a debug.print statement inside your loop and see what the control values are. This should help isolate the problem. Some of the controls may be linked to numbers and default to zero in which case they won't be null.
If your subform is found within the main form then the following code is an example of how I was able to run a query from criteria in the main form and have the subform display the data found.
' The following lines of code will set the control source of each field in the subform fields to the correct value.
[TempQuery].Form![Customer ID].ControlSource = "Customer ID"
[TempQuery].Form![Customer Account Number].ControlSource = "Customer Account Number"
[TempQuery].Form![Company Name].ControlSource = "Company Name"
[TempQuery].Form![Contact Person].ControlSource = "Contact Person"
[TempQuery].Form![Company Address].ControlSource = "company address"
' The following sets up the DAO connections (DAO3.6)
Dim dbs As Database, rs As Recordset
Set dbs = CurrentDB
' Note here that I am dimming frmtempform as a form. I will use this later when setting the subform's recordsource property to the SQL criteria.
Dim frmtempform As Form
Dim rst As String
Dim rsst As Recordset
' Here I check the value of the search text box for criteria. In this case, I want to know if the value is numeric, if so I would then run the following code.
If IsNumeric(Me.txtSQLSearch) Then
' SQL Criteria
rst = "SELECT * FROM [clientinfo] where [customer ID] = " & _
Me.txtSQLSearch & ""
Set rs = dbs.OpenRecordset(rst, dbOpenDynaset)
' Here I set frmtempform to my sub form
Set frmtempform = Form_TempQuery
' With frmtempform, I then set the recordsource to my SQL criteria and check to see if my query returned any records. If not, I simply let the the user know, via a messagebox message and refresh the subform.
.RecordSource = rst
Set rsst = [TempQuery].Form.RecordsetClone
If rsst.BOF And rsst.EOF Then
prompt = MsgBox("No Records Match Your " & _
"Criteria.", vbOKOnly, "No Match")
This is only a snippet of the total procedure written into my personal database - the relevant portion. It's a bit confusing at first, but it seems to work really well when dealing with subform display of search criteria.