Results 1 to 12 of 12
  1. #1
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25

    Help Module Receive information from table

    Hello, can someone help me out.


    I have a few tables
    Ex:
    H243-L
    H243-R
    V177
    and other

    I would like when press the button to find inside from specific table name , specific fields are they empty "" if they are empty will do something else will do other.

    Code:
    Sub CheckDB()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim truenot As String
    showme = 0 ' default value
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Select * From [H243-L] where ver1_2=""")
    Do Until rst.EOF
    showme = 1
    If showme = 0 Then
    showme = 0
    'MsgBox "Field Name: " & showme
    Else
    showme = 1
    'MsgBox "Field Name: " & showme
    End If
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    I have try this code but always returns me 0.
    Inside table names i have fields: ver1_2, ver1_3, ver2_2, ver2_3, ver3_2, ver3_3 .. and i want if they are left empty to open the different report.

    So what i want to do is , if in $table has this columns that are empty "" , to open specific report
    DoCmd.OpenReport "Print_W177-L", acViewPreview
    else if there isnt empty fields in this table then will open another report.

    Can someone help me how to make function like that?
    Thank you.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,279
    It will do because you set it to 1 just before your if statement

  3. #3
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25
    yes the meaning was if the select ver1_2 and others are "" empty to be showme = 1.
    and then if showme is 1 then will open report EX for example but if its showme = 0 if this fields are not empty will open other report when press button. This is my problem how to make it

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,373
    Maybe use recordcount for your process

    Set rst = db.OpenRecordset ("select * From H243-L where ver1_2=""")
    If rst.recordcount <> 0 Then
    Open Report1
    Else
    Open Report2
    End If

  5. #5
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25
    So i did it like this:
    Code:
    Public showme As Integer        'Declare public string
    
    
    Sub CheckDB()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim truenot As String
    'showme = 0 ' default value
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * From [H243-L] where ver1_2="" ")
    If rst.RecordCount <> 0 Then
    'Open Report1
    showme = 1
    Else
    'Open Report2
    showme = 0
    End If
    
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
    
    End Sub
    and in my button form code i use like this:
    Code:
    If showme = 1 Then
          MsgBox "Result is: " & showme
            'DoCmd.OpenReport "Print_W177-L", acViewPreview
            'If result = 1 Then
            'DoCmd.PrintOut , , , , Text19.Value, False
            'End If
          Else
          MsgBox "Result is: " & showme
         End If
    But it returns showme = 0 always

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Why not simply use dCount?
    Code:
    If dcount("*","[H243-L]", " IsNull([ver1_2])=True")>0  Then
         Docmd.OpenReport "Print_H243-L",acViewPreview 'table has empty ver1_2 records
    Else
        'do something else - table doesn't have empty records
    end If
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25
    Quote Originally Posted by Gicu View Post
    Why not simply use dCount?
    Code:
    If dcount("*","[H243-L]", " IsNull([ver1_2])=True")>0  Then
         Docmd.OpenReport "Print_H243-L",acViewPreview 'table has empty ver1_2 records
    Else
        'do something else - table doesn't have empty records
    end If
    is there an option that from Query to get the selected code and use it in dcount after ver1_2 to have where select = $select .
    It means that i have a specific lines that i made with select 3 , and want to search the code from this select does it has ver1_2 empty field. is it possible?
    Click image for larger version. 

Name:	Capture.PNG 
Views:	25 
Size:	14.2 KB 
ID:	45393

    And my table will store this information and example i put on some lines Select 3 in table, and Query will take the select that i type in.
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	25 
Size:	13.4 KB 
ID:	45394

  8. #8
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25
    So i decide to do it like this:
    Code:
    Public Sub CheckTable(ByVal place As String)
    
    
    If DCount("*", place, " IsNull([ver1_2])=True") > 0 Then
         'table has empty ver1_2 records
         showme = 0
    Else
        'do something else - table doesn't have empty records
        showme = 1
    End If
    
    
    End Sub
    and here user will input manually the select number:
    Code:
    Sub InputBoxTest()
        inputData = InputBox("What is your select?", "Select ID")
        '
        ' Check to see if any data was entered
        '
        If inputData = "" Then
            '
            ' If so, display it
            '
            'MsgBox inputData
            MsgBox "Please enter some number"
        End If
    End Sub
    So my question is how i can add inside "If DCount("*", place, " IsNull([ver1_2])=True") > 0 Then" also to search with Select = inputdata

    So to become like this:
    - search select = inputdata , if its matching with Table data, then search if ver1_2 is null and if it has then do something else other.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    If DCount("*", place, " IsNull([ver1_2])=True AND [Select]='" & inputdata & "'") > 0

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    luckydead is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    25
    Quote Originally Posted by Gicu View Post
    If DCount("*", place, " IsNull([ver1_2])=True AND [Select]='" & inputdata & "'") > 0

    Cheers,
    This not work correct.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    What is the result? You need to show the entire code and whatever queries you are using otherwise we'll not be able to help much with just bits like this....
    How\where you declare the inputdata variable? The InputBox function returns a string so inputdata as per your code is a string. But the [select] field looks like is a number (hard to say as you have it centered instead of right aligned) so you might need to wrap the inputdate in CInt:
    Code:
    DCount("*", place, "IsNull([ver1_2])=True AND [Select]=CInt(" & inputdata & ")") > 0
    
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Synergy.ron@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    81
    I too have wrestled with this problem. For me, the solution was in using recordset properly. I suggest you watch Steve Bishop's excellent Youtube videos.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. If you can't receive emails from this forum
    By Matrix in forum Forum Suggestions
    Replies: 0
    Last Post: 08-15-2019, 08:16 PM
  2. Replies: 12
    Last Post: 02-01-2019, 04:47 PM
  3. Replies: 7
    Last Post: 01-07-2019, 02:55 PM
  4. Receive Data from external IP on Network over Port
    By WhiteBearMike in forum Programming
    Replies: 1
    Last Post: 10-19-2018, 04:34 PM
  5. Control cannot receive focus
    By numberguy in forum Forms
    Replies: 2
    Last Post: 06-25-2012, 12:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums