Amanda-
What is the Record Source of the form on which you are removing the filter?
I see below you tried to attach a picture - you can't do that in messages in this forum. You can upload your picture to the Files / 2_Assistance Needed folder if you think it would help solve your problem.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of amandabyrne@carolinatigerrescue.org
Sent: Sunday, September 08, 2013 5:04 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: RE: writing a new record via the VBA
Yes, not sure why the new google groups isn't formatting text.
I added your
Me.FilterOn = False
Me.Requery
This does not help, I created a MsgBox after the Requery, and it does indicate that Me.FilterOn is equal to False, but the form does not return results
Any other ideas?
--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:
Wow! Yahoo really messed up your post. I had to copy it into an HTML editor to read it.
Try turning off the filter before you do a Form Requery which actually should be coded as Me.Requery.
So what I am proposing is this:
Me.FilterOn = False Me.Requery
--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:
I have a continuous form that displays a set of records from my Animals table that displays a subset of animals based on search criteria set from unbound fields in the form header. (It does this by manipulating the filter and turning it on)
I have another set of unbound fields in the header that is intended to set values for adding a journal record (in the Animal Journal table, related by Animal ID) for each of the animals listed in the continuous form.
I originally created a button to run a SQL statement with a DoCmd.RunSQL (statement) command. It loops through the records in the continuous form, running the "Insert" The command does add the records to the database, but the form then locks up, so I cannot use the form to select another set of animals.
By "locking up", I mean that the Label_Preview that sets the filter does not appear to affect the form's results anymore, and the results in the form result show "#Name?" as if the form needs information to display the results
Is there something I should do to reset the form's filter? Should I be using something besides a SQL command to add the records (like a acNewRec or something?)
(screenshot attached)
Relevant code:
Private Sub Form_Load()
Me.Combo_Population = "Current Animals"
Me.AJ_date = Date
Me.FilterOn = True
End Sub
Private Sub Label_Preview_Click()
' this is a text link that the user clicks on to preview the results of his subset of animals that will receive new journal entries
On Error GoTo Err_Label_Preview_Click
Dim strWhere As String
Dim IngLen As Long
strWhere = ""
prevDesc = ""
(then a bunch of code that sets the strWhere value)
If IngLen <= 0 Then
strWhere = ""
Else
strWhere = Left$(strWhere, IngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
Exit_Label_Preview_Click:
Exit Sub
Err_Label_Preview_Click:
MsgBox Err.Description
Resume Exit_Label_Preview_Click
End Sub
Private Sub AJ_Click()
' this is a button that adds a record to the Journal table based on each Animal record in the filtered form
On Error GoTo Err_AJ_Click
Dim RecordsChanged As Integer
Dim rsAnimals As DAO.Recordset
RecordsChanged = 0
strDesc = "Journal Entry: " & Me.AJ_date & ", " & Me.AJ_Type & ", " & Me.Note
Me.Session_Log = Me.Session_Log & vbCrLf & strDesc & vbCrLf & "Added to "
Set rsAnimals = Me.Recordset
rsAnimals.MoveFirst
Do While Not rsAnimals.EOF
If Me.Journal_Toggle = True Then
Dim statement As String
Dim AJDate As Date
statement = "Insert into [Animal Journal] ([AJ Date], [Animal ID], [AJ Type], [Note], [Timestamp]) Values ('" & Me.AJ_date & "', '" & Trim(Me.Animal_ID) & "', '" & Me.AJ_Type & "', '" & Me.Note & "', '" & Date & "');"
RecordsChanged = RecordsChanged + 1
DoCmd.RunSQL (statement)
Me.Session_Log = Me.Session_Log & vbCrLf & " " & Me.House_Name
End If
rsAnimals.MoveNext
Loop
rsAnimals.Close
Exit_AJ_Click:
Exit Sub
Err_AJ_Click:
MsgBox Err.Description
Resume Exit_AJ_Click
Form.Requery
End Sub
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar