Hide a button when a record is found in the database

[wim1981]

Hi everyone,

I want to show/hide a button on my form.
The button is for entering a new record in the table of the database.

Now when i Load the form, i want to check if there are already records in the table for a specific lanID. When there are no records, the butten has to be displayed. When there are already records for that lanID the button has to be hidden.

I have found some code to tackle that, but When i use the rs.MoveLast it hides the button when there are records, but when there are no records it gives an error that there are no records found.

Is there another way to do this? Oh I almost forgot to say that i use Access 2010

Here is the code that i use:

Private Sub Form_Load()
Dim SQL As String
Dim rs As DAO.Recordset
Dim landmeterID As String

landmeterID = [Forms]![MainForm]![LanIDTxt]

SQL = "select * from dbo_Lan_Opleiding where Id_landmeter ='" & landmeterID & "'"
Set rs = CurrentDb.OpenRecordset(SQL)

rs.MoveLast
If rs.RecordCount = 0 Then
'no rows
NewOplBtn1.Visible = True
Else
'rows returned
'BtnNewOpl1.Visible = False
NewOplBtn1.Visible = False
End If
End Sub

Thanks a lot
Wim

 

[pbaldy]

MoveLast will error on an empty recordset. Test first for EOF.

 

[wim1981]

MoveLast will error on an empty recordset. Test first for EOF.

If i set rs.EOF before rs.MoveLast it won't work. Either with only rs.EOF it won't work

 

[Minty]

I used this in the on current form event form for setting previous and next buttons, where sSql is your record source;

Set rs = db.OpenRecordset(sSql, dbOpenDynaset, dbSeeChanges)
 If Not (rs.BOF And rs.EOF) Then
        On Error Resume Next
        rs.MoveLast
        rs.MoveFirst
        On Error GoTo 0
        iRcount = rs.RecordCount
 End If
    
 If Me.CurrentRecord = 1 Then
    Me.cmdPrev.Enabled = False
 Else
    Me.cmdPrev.Enabled = True
 End If
    
 If Me.CurrentRecord = iRcount Then
    Me.cmdNext.Enabled = False
 Else
    Me.cmdNext.Enabled = True
 End If

 

[pbaldy]

If i set rs.EOF before rs.MoveLast it won't work. Either with only rs.EOF it won't work

I'm not sure what you're trying. You don't set EOF, you test for it.