Addnew and Edit (1 Viewer)

[arunakumari02]

I have a Grid of 3*3 textboxes.

If I enter a new value in grid it should add a row in the table or if I update the existing value in the grid it should edit that record in the table.

I am trying to first find whether that record is there in the entire table then if it does not add the record. If you find it edit the record.

The code below is not working when I edit existing value it is not editing in table but adding a new record?

Any help is appreciated.

[FONT=Times New Roman][SIZE=3]Set rstAvailability = CurrentDb.OpenRecordset(strAvailability)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Dim strCriteria As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                  "[ResourceID]" = Forms!filter("txtResourceID" & intRow)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  [/FONT][/SIZE][SIZE=3][FONT=Times New Roman]    rstAvailability.FindFirst (strCriteria)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     [/FONT][/SIZE][SIZE=3][FONT=Times New Roman]    If rstAvailability.NoMatch = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Debug.Print "addnew"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Else[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Debug.Print "edit"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]

 

[ErikSnoek]

Looks fine at a first glance. May I ask where you placed this code?

edit: Ah! How could I miss this.

[FONT=Times New Roman][SIZE=3]    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]                  "[B] AND[/B] [ResourceID]" = Forms!filter("txtResourceID" & intRow)[/FONT][/SIZE]

 

[arunakumari02]

In a function and called that function in MS-Access.

I was also trying in different way but i get error saying too few parameters expected.

Here in strSQL I get only one record if match is there or no record if match is not there.

Public Function Edit(intRow As Integer, intMonth As Integer)
 
strSQL = "SELECT MonthAvailable, ResourceID FROM tblResource WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
"AND txtResourceID =" & Forms!filter("txtResourceID" & intRow)
 
[COLOR=darkorange]Set rstAvailability = CurrentDb.OpenRecordset(strSQL) -----error too few parameters. Expected 1[/COLOR]
If IsNull(rstAvailability) Then
Debug.Print "addnew"
 
Else
Debug.Print "edit"
End If
 
End Function

Any help is really appreciated.

 

[ErikSnoek]

Check my post, I edited it

 

[arunakumari02]

Hey,

Still have an issue with the code.

Public Function Edit(intRow As Integer, intMonth As Integer)
Set rstAvailability = CurrentDb.OpenRecordset("tblResource")
    Dim strCriteria As String
    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _
                  " AND [ResourceID]" = Forms!filter("txtResourceID" & intRow)
    

    [COLOR=red]rstAvailability.FindFirst (strCriteria) -- ERROR ---Operation not supported for this type of object.
[/COLOR]     Debug.Print rstAvailability.NoMatch
    If rstAvailability.NoMatch = True Then
     Debug.Print "addnew"
    Else
    
    Debug.Print "edit"
    End If

End Function

 

[ErikSnoek]

Change

    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _
                  " AND [ResourceID]" = Forms!filter("txtResourceID" & intRow)

to

    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _
                  " AND [ResourceID]=" & Forms!filter("txtResourceID" & intRow)

 

[arunakumari02]

Hey I changed the code but still getting error.

Public Function Edit(intRow As Integer, intMonth As Integer)
Set rstAvailability = CurrentDb.OpenRecordset("tblResource")
    Dim strCriteria As String
    strCriteria = "[MonthAvailable]=" & Forms!filter("txtMonth" & intMonth) & _
                  " AND [ResourceID] =" & Forms!filter("txtResourceID" & intRow)
    

    [COLOR=red]rstAvailability.FindFirst (strCriteria) -- ERROR ---Operation not supported for this type of object.
[/COLOR]     Debug.Print rstAvailability.NoMatch
    If rstAvailability.NoMatch = True Then
     Debug.Print "addnew"
    Else
    
    Debug.Print "edit"
    End If

End Function

Any help?

 

[ErikSnoek]

Did you check strCriteria before it goes to the FindFirst line? Is it ok? Or maybe it is because you didn't declare rstAvailability, add "Dim rstAvailability As DAO.Recordset" on top.

 

[arunakumari02]

I checked strCriteria gives moth available and resourceID and I also declared rstAvailability.

But not knowing where the problem is?

I was also trying another way but having error.

Public Function Edit(intRow As Integer, intMonth As Integer)
 
strSQL = "SELECT MonthAvailable, ResourceID FROM tblResource WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
"AND txtResourceID =" & Forms!filter("txtResourceID" & intRow)
 
[COLOR=darkorange]Set rstAvailability = CurrentDb.OpenRecordset(strSQL) -----error too few parameters. Expected 1[/COLOR]
If IsNull(rstAvailability) Then
Debug.Print "addnew"
 
Else
Debug.Print "edit"
End If
 
End Function

Any help please.

 

[ErikSnoek]

Well in that code you should change the sql part to

strSQL = "SELECT MonthAvailable, ResourceID FROM tblResource WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
" AND ResourceID =" & Forms!filter("txtResourceID" & intRow)

 

[arunakumari02]

I did not find any difference in the sql statement.

 

[ErikSnoek]

Yours:

strSQL = "SELECT MonthAvailable, ResourceID FROM tblResource WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
"[U][B]AND[/B] [B]txt[/B][/U]ResourceID =" & Forms!filter("txtResourceID" & intRow)

Mine:

strSQL = "SELECT MonthAvailable, ResourceID FROM tblResource WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
" AND ResourceID =" & Forms!filter("txtResourceID" & intRow)

 

[arunakumari02]

The does not work properly.

If there is no record in the table and when I enter the value it goes to edit instead of adding new record.

Public Function Edit(intRow As Integer, intMonth As Integer)
strSQL = "SELECT MonthAvailable, ResourceID FROM tblResourceAvailability WHERE MonthAvailable = " & Forms!filter("txtMonth" & intMonth) & _
          "AND ResourceID =" & Forms!frmfilter("txtResourceID" & intRow)
        
Set rstAvailability = CurrentDb.OpenRecordset(strSQL)
Debug.Print IsNull(rstAvailability)
If IsNull(rstAvailability) Then
    Debug.Print "addnew"
    
Else
     Debug.Print "edit"
End If
End Function

 

[ErikSnoek]

If IsNull(rstAvailability) Then

should be

If rstAvailability.eof Then