Table reference in VB Code

[EPD Hater]

How does one reference a table in VB code/event procedure? What I want to do is have a Message Box popup when a button is clicked saying that there are no records if a certain table has no rows. (In a previous post, I mentioned that the form would be blank if the control source table had no rows which confused me.)

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=46425

 

[pono1]

E,

One way is to use the mildly unpopular but relatively easy to use DCount function.

'set up a variable in memory
     Dim lngCount As Long

'assign the record-count of a table named TblProps,
'(where PropID is the Primary Key)
'to that variable
     lngCount = DCount("PropID", "TblProps")

'No records (rows)?
    If lngCount = 0 Then
        Exit Sub 'then leave
     End If

'Otherwise
'Keep going with whatever code may lie below...

Regards,
Tim

 

[EPD Hater]

Keep in mind, I'm trying to wing it here with limited resources. What I want to do is popup a message box saying there is no records if the table [Committed Data] is empty. But if there are records, then open the form "areyousure".

When I click the Export button, nothing is happening.

 Private Sub export_Click()

     Dim lngCount As Long
     lngCount = DCount("SSN", "Committed Data")
     If lngCount = 0 Then
        Response = MsgBox("There are no records to export.", 64, "No Records")
            If Response = vbOK Then
                DoCmd.CancelEvent
            End If
     ElseIf lngConut > 0 Then
        DoCmd.OpenForm "areyousure"
     End If
 End Sub

 

[RichMorrison]

you wrote
<<
ElseIf lngConut > 0 Then
>>
Did you actually copy and paste code into your message. If yes you should try "Option Explicit".

Conut <> Count

RichM

 

[EPD Hater]

Thanks,

Not sure what the option does, but maybe I should start reading up on what some words mean. I fixed the typo too. Thanks a lot!

 

[pono1]

For better or worse, an anal-retentive approach usually wins out when writing code.

My suggestion: Relax -- breathe in, breathe out -- and start over.

Use the snippet I gave you --- no other code.

And test it. Does it run like you expect?

If it does, add one line. Just one. And test again. When you feel like it's on-track, add another line...

I've made one correction to the sample below and notated the spot where I think you can add the next line...

'set up a variable in memory
     Dim lngCount As Long

'assign the record-count of a 
'table named "Committed Date"
'to that variable
     lngCount = DCount("SSN", "[Committed Date]") 
'Note: put names with spaces in brackets

'TEST -- see the Immediate Window 
'          on your VBA screen after the code runs
debug.print lngCount
'TEST

'No records (rows)?
    If lngCount = 0 Then

	'Put a "no records" message box here and test

        Exit Sub 'scram -- nothing more to do here
     End If

'Otherwise
'Keep going with, say, a Docmd.FormOpen... line...

Regards,
Tim