Results 1 to 7 of 7

Thread: My VBA

  1. #1
    Join Date
    Aug 2003

    Unanswered: My VBA

    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 strSQL
    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?
    End If
    End If


    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)
    Exit Sub
    End If

    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

    DoCmd.OpenForm "FrmSearchResult"
    Me!FrmSearchResultsub.RecordSource = strSQL

  2. #2
    Join Date
    Mar 2002
    Bielefeld, Germany

    Can you tell us how your forms are arranged?

    Is FrmSearchResultsub a subform of FrmSearchResult?
    The code you posted is in which form?

    Assumed you have FrmSearchResult as a new form and FrmSearchResultsub as a subform of it, the code should look something like this

    DoCmd.OpenForm "FrmSearchResult"
    Forms!FrmSearchResult!FrmSearchResultsub.Form.Reco rdSource = strSQL

  3. #3
    Join Date
    Aug 2003

    Re: Can you tell us how your forms are arranged?

    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.

  4. #4
    Join Date
    Aug 2003
    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.
    With frmtempform
    .RecordSource = rst
    Set rsst = [TempQuery].Form.RecordsetClone
    If rsst.BOF And rsst.EOF Then
    prompt = MsgBox("No Records Match Your " & _
    "Criteria.", vbOKOnly, "No Match")
    End If
    End With

    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.



  5. #5
    Join Date
    Aug 2003

    Talking chrisp_999 - Thank you!

    As always I was close but not quite there - it can be so frustrating

    Thank you - that bit of it now works fine

  6. #6
    Join Date
    Jul 2003
    You checked not for the text in the control, but for the name of the control, that never is null (If Not IsNull(ctl.Name) Then fGotOne = True ).


  7. #7
    Join Date
    Aug 2003

    Talking SubHugo - Got it!

    Changed the code to:

    If Not IsNull(ctl.Value) Then

    & this has done it. I only need to tweek the sql and all is working

    Thank you for all the suggestions and help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts