Querying and Editing table from a form

[grenee]

Good Day All.

I have a form with a button, a textbox1 and some other textboxes

Basically when the button is pressed I want the event to cause a table to open with only records of the value in the textbox1. I then want to add a new record using values from the textboxes on the form to the new record fields

This is the code I have tried but it is not working. Can anyone assist?

Option Compare Database
Private Sub Command26_Click()
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset

Set curDatabase = CurrentDb

Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin] ")

If Not rs.RecordCount = 0 Then

rs.MoveLast
rs.MoveFirst
End If

rs.AddNew
rs.Fields(1) = Now()
rs.Fields(2) = Form_frmSignin.txtUser

rs.Fields(3) = Now()
rs.Fields(4) = Now()

rs.Fields(8) = Now()
rs.Fields(9) = "AutoSave"

If rs.RecordCount Mod 2 = 1 Then

rs.Fields(6) = "IN"
Else: rs.Fields(6) = "OUT"
End If

rs.Update

Set curDatabase = Nothing

Set rs = Nothing

End Sub

 

[JHB]

..
it is not working.

Clarify "not working", do you get an error or what?

 

[grenee]

Thanks for your response.

I however submitted the incorrect code. This is what is should have been:
The error message is : The engine cannot find input table or query.

Option Compare Database
Private Sub Command26_Click()
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset
Dim strfilter As String

Set curDatabase = CurrentDb
strfilter = "officerName= ' " & Me.txtUser & " ' AND [Auto Date] = # " & Date & "#"
MsgBox strfilter
Set rs = CurrentDb.OpenRecordset(strfilter)

If Not rs.RecordCount = 0 Then

rs.MoveLast
rs.MoveFirst
End If

rs.AddNew
rs.Fields(1) = Now()
rs.Fields(2) = Form_frmSignin.txtUser

rs.Fields(3) = Now()
rs.Fields(4) = Now()

rs.Fields(8) = Now()
rs.Fields(9) = "AutoSave"

If rs.RecordCount Mod 2 = 1 Then

rs.Fields(6) = "IN"
Else: rs.Fields(6) = "OUT"
End If

rs.Update

Set curDatabase = Nothing

Set rs = Nothing
MsgBox "The time sheet has been submitted.", _
vbOKOnly Or vbInformation, "Kolo Bank"
Form_frmSignin.Visible = False
End Sub

 

[JHB]

You only have the criteria when you open the recordset, you need a table/query name also and the "WHERE" .

[COLOR=Blue]Set rs = CurrentDb.OpenRecordset([B][COLOR=Red]"Select * from [YourTableORQueryName][/COLOR][/B] [B][COLOR=Red]WHERE "[/COLOR][/B] &  strfilter)
     [/COLOR]

 

[grenee]

Thanks. I got it working

 

[grenee]

You have been very helpful

 

[JHB]

You're welcome, good luck.

 

[grenee]

Thanks.

I just forgot one thing: in my original code above in Blue I had a lengthier query which was as follows:

strfilter = "officerName= ' " & Me.txtUser & " ' AND [Auto Date] = # " & Date & "#"

txtUser is a control on the form.

I subsequently left out the OfficerName to make it easier to get an explanation, which I did. However I am trying now to add back this omission but there seems to be a problem. I think I have to work on the & sign and "" a bit more. Anyhow here is one of my attempts:

strfilter = " [OfficerName] = me.txtUser & 'And' & DateValue([Auto Date]) = # " & DateValue(Date) & "#"

Any suggestion for this not working?

 

[JHB]

strfilter = " [OfficerName] ='" & me.txtUser & "' And DateValue([Auto Date]) = #" & DateValue(Date) & "#"

Try the above.