form subform

[sparkbyte]

I have a form for deleting records that I need some help with.

the main form has an unbound text box that is used to enter reciept numbers (comma delimited).

A submit button that run a SQL stored procedure using the input recipt numbers.

what i need to know how to do is, how do I return the resultset to a subform that allows the user to check a box next to each entry confirming what they intend to delete?

Thanks

John Fuhrman

 

[dhookom]

The only way to use a checkbox in a continuous form is to have it bound to a field in the record source. An alternative is to use a multi-select list box.


Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

OK, so how would I get the resultset to the multiselect list box?


Thanks

John Fuhrman

 

[dhookom]

Generically:

Dim strSQL as String
strSQL = "SELECT FieldA, FieldB, FieldC FROM tblNoName " & _
    "WHERE FieldA IN (" Me.txtBox & ") ORDER BY FieldB"
Me.lboListBox.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If you want checkboxes, I like to use a listview.
However, there is a lot more coding involved. You need to be proficient with recordsets. The multiselect will be much easier.

But if interested can provide some code and examples.

 

[sparkbyte]

I'll have to see which I can get going.

Here is the code for the button.

Private Sub SubmitforDelete_Click()
 Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   With cn
        .ConnectionString = "DRIVER=SQL Server;SERVER=Z02sqcnsc02;Database=nsc_sql;Trusted_Connection=yes;"
        .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "dbo.usp_Get_Psafety_Case_By_FDNS" & " '" & Forms![Delete_Letter_Detail_Main]![FDNSnumbers] & "'"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   

' \\  Need to change this to fill in the sub form  //   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing

End Sub

Thanks

John Fuhrman

 

[dhookom]

I would use a pass-through query that is the Row Source of the multi-select list box since I don't see any mention of a yes/no field returned by your SP.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya sparkbyte . . .

If you use the [blue]MultiSelect Listbox[/blue] suggested by [blue]dhookom[/blue], you won't need the textbox. Users just make their selections. From their its a simple matter of looping thru a delete query/SQL ... probably thru a button.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[sparkbyte]

The process That i am trying to do is this.

Allow the department supervisor(s) to enter a value for one of the primary identifying fields (there are three) or a list of values (####,####,####) which is used to retrieve a list of recrods that match the search criteria (could be more that one returned for each value) and allow the supervisor to select the intended record to be removed.

This is the reason for the tabular display. This way the supervisor can identify the full record befor confirming the delete.

I had another thought for a solution.

Use the current USP to get a list of matching records, Add a new bit value field to the resultset retuned, then use that bit value as the selection (yes/no) on the delete. Or maybe use an unbound field on the form for the selection and just retrieve the record ID for the delete.

Just not sure how to build a form like this. I have not had the need until now, and I cannot seem to find any examples to learn from.

THANKS!!!

Thanks

John Fuhrman