Well done, sorry I wasn't around, but I have been busy.
Well done, sorry I wasn't around, but I have been busy.
My approach to the second problem has been included in the code below (which also includes the first solution).
[vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Dim checkline As Variant
Dim myconnection As ADODB.Connection
Dim myDeletePull As New ADODB.Recordset
Set myconnection = CurrentProject.Connection
myDeletePull.ActiveConnection = myconnection
myDeletePull.Open "TblOrderPull", , adOpenDynamic
Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid
myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
'updates TblSaleOrderLine!saleprocessed from 1 (true) to 0 (false)
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
Me.[saleprocessed].Value = False
myRecordChange.Update
'deletes any instances in TblOrderPull where salesorderline = myorderline
myDeletePull.MoveFirst
While Not myDeletePull.EOF
checkline = myDeletePull.Fields("salesorderlineid").Value
If checkline = myorderline Then
MsgBox "Found at id: " & myDeletePull.Fields("pullid").Value
myDeletePull.Delete
Else
End If
myDeletePull.MoveNext
Wend
End If
myDeletePull.Close
myRecordChange.Close
Set myDeletePull = Nothing
Set myRecordChange = Nothing
End Sub[/vba]
The line [vba]myDeletePull.Delete[/vba] needs to delete the current row of data. I thought that it would work, but I get a Run-time error "3251": Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
So what's the solution I need here, in order to delete the current record?
Ah got the problem.
[VBA]myDeletePull.Open "TblOrderPull", , adOpenKeyset, adLockOptimistic[/VBA]
Is it "Solved" now?
Almost, which is why I haven't changed the status yet. (That way I can still add posts)
For example:
I now want to add some buttons on the main form, that control the subform; such as GoToRecord.
I've added buttons that do the following:
DoCmd.GoToRecord , "FrmConsignee", acNext
DoCmd.GoToRecord , "FrmConsignee", acPrevious
with "FrmConsginee" being the name of the subform.
The buttons occur on the main form.
However when I press the buttons, I get the following error:
"The object "FrmConsignee" isn't open."
I've also tried using "Me.FrmConsignee" again without luck.
Try it like this with your Form and field names
Me![WorkHardware Subform].SetFocus
Me![WorkHardware Subform].Form![Combo6].SetFocus
DoCmd.GoToRecord , , acNext
Not sure I get what you mean.
The cosde I currently have (which doesn't work) is:
[VBA]Private Sub bttn_gotofirstdelivery_Click()
On Error GoTo Err_bttn_gotofirstdelivery_Click
DoCmd.GoToRecord , "Me.FrmConsignee", acFirst
Exit_bttn_gotofirstdelivery_Click:
Exit Sub
Err_bttn_gotofirstdelivery_Click:
MsgBox Err.Description
Resume Exit_bttn_gotofirstdelivery_Click
End Sub[/VBA]
The parent form (Me) is called: FrmSupplier
The subform is called: FrmConsignee
The buttton to call the above code is called: bttn_gotofirstdelivery
The above code is on the parent forms code.
In your code below, what is Combo6 ?
Combo6 is the first field on my Subform, what the code does is set the focus to the subform and then to the first field on that subform and THEN does the docmd.gotorecord.
Gotcha, thanks.
Is it possible to create a form that the user can use to perform an advanced filter on the results of another form?
For example, when the user goes from the Main Menu, into the "Edit Records", they are first directed to a form, which includes various fields (I guess they should be dropdowns - and a calendar). If the user completes any of the dropdowns, or selects a date on the calendar then the records are sorted accordingly.
I'm certain this can be done, but having written the above, it seems more complicated than I initially thought.
Suggestions please?
Yes of course, you can do it by Query, Form Filter or by VBA generated SQL recordset.
Take your pick.
I guess the question should be which is the most effective, and requires the least Mb?
Can you help me with an example (of the most effective method)?
I would suggest the Form Filter method provides a flexible system as the records to be viewed/edited casn be on a subform on the "filtering" form.
Take a look at this example called Search 2000 that I posted on this thread.
http://www.vbaexpress.com/forum/showthread.php?t=25027
Ecellent, the Allen Browne database seems very helpful, and I like the fact I don't need an extra form, but instead that I can put them directly on the form.
BUT that does lead to another question.
I use the same form, regardless of whether I am adding a new record, or editing an old record.
However, I only want the search criteria to appear on existing records (the ones I can edit)- but not to appear if I am adding a new record (as that could confuse the end user).
How can I do this? How can I tell the form not to show the search criteria if it's showing the new record?
Thanks as always
You use VBA code in the On Current event to set those fields & Combos to Visible or Invisible. It goes like this
If Not me.newrecord then
me.combonumberorname.Visible = True
me.fieldname.Visible = True 'etc
else
me.combonumberorname.Visible = False
me.fieldname.Visible = False 'etc
End if
Where combonumberorname are the actual names of your combos and fieldname are the names of your fields, if you have any.
Great thanks,
That's a big help.
I'm on holiday until 4th Jan now, so I'll be keeping this thread as unsolved until I'm sure I'm done asking questions about it.
Happy Holidays!
I've got another question about the filter. (I know, I should be on holiday!)
If the filter doesn't return any results, it just shows the new record. (And because of the way I've programmed it, it then won't show the filter options, so I can't then clear the irrelevant filter.
Is there some smart piece of code I can use so that if the filter is run, and no results are shown, it just posts a message stating that no results were found, and then the filter is reset.
(I'm still using Allen Browne's filter system)
Thanks, and again, happy holidays.
You can check the "Current" record's key field and if it null use a msgbox to tell the user and then set Filter off.
What code would I use to NOT print the first page of a report?