Continuous Form Woes

[Paul H]

I have a continuous form with the following Record Source.

SELECT tblHousingEvents.* FROM tblHousingEvents WHERE tblHousingEvents.ccs=[Forms]![frmBenefits]![cboClientID];

We want to list all the existing housing event for the selected ID and also allow data entry for any new housing events. The following code runs in the After Update event of cboClientID.

[CODE][
Private Sub cboClientID_AfterUpdate()
On Error GoTo Err_Handler


Me.txtFirstName = Me.cboclientID.Column(2)
' Me.Requery
' Me.Refresh
DoCmd.GoToRecord , Me.Name, acLast


Err_Handler:

If Err.Number = 3314 Then
Me.Undo
Me.txtFirstName = ""
Me.cboclientID.SetFocus
MsgBox Err.Number & ": " & Err.Description
End If
End Sub
/CODE]

If we leave the me.Requery out, the code runs but does not show any history. It immediately starts a new record.
If I run ME.Requery or Me.Refresh we get the following error, even though tblHousingEvents.Address field is populated in the one record that the client has in that table.



One other problem to solve. The form prompts for a client ID when it first loads, though loading from design view doesn't present this prompt.

Let me know if you have any questions and thanks in advance.

Paul

[aytee111]

Is this a main form/subform design? If so, the cboClientID AfterUpdate should be:
me.filter="ClientID=" & me!cboClientID
me.filteron=true
me.requery

The record source for the subform should be the table name. The Link properties for the subform should be set to ClientID

[Paul H]

No, the combo box is in the header and uses tblClient as a source. I've considered going to a subform.

[aytee111]

If there is no subform, set the filter as previously described, that should be enough. Remove the criteria from the record source.

[Paul H]

I made progress at first, but it wouldn't quite work. Despite the combobox being labeled cbo.ClientID, the actual field it is looking at is a text field called CCS, so I adapted your code:

On Error GoTo Err_Handler
    Me.txtFirstName = Me.cboclientID.Column(2)
    Me.Filter = "CCS = '" & Me!cboclientID & "'"
    Me.FilterOn = True
    Me.Requery

Err_Handler:
        
    If Err.Number = 3314 Then
        Me.Undo
        MsgBox Err.Number & ": " & Err.Description
    End If
        
End Sub

As soon as I update the combo box I get the same error I did before. The line of code it breaks on is when I set the filter.

Me.Filter = "CCS = '" & Me!cboclientID & "'"

I also added this code to set up the form to load empty. That seems to run OK.

Private Sub Form_Load()
    Me.Filter = False
    Me.FilterOn = True
    Me.Requery
    Me.cboclientID.SetFocus
End Sub

[aytee111]

Is it possible that "address" is a required field on the table? Then when you try and move to another record by using the filter it won't let you because the record that you on has a blank address.

[rpeare]

Do you have any fields set with a default value (I can't tell if txtfirstname is a data entry field or not from this code)? If your default values are populated when the form opens you may have to remove that value (or set it programmatically rather than relying on the 'default value' setting of the text box) then issue your 'cancel record' command before refreshing your data.

Me.txtFirstName = Me.cboclientID.Column(2)

this line looks to me like you're creating a default value in your data entry which may be your problem and you may want to try me.txtfirstname = null rather than me.txtfirstname = "" (null value rather than an empty string).

[Paul H]

I am starting with a blank slate. No records, no filters except False.




As soon as I select a Client (ccs) from the combo box, this happens.



I want to see all existing records in the Housing Events table with that Client ID. Address is a required field and I can see that the form is in Edit mode, but I haven't tried to key a new record yet. I also can't see the existing records for that client ID.

[aytee111]

Try adding a "Me.Undo" before the filter.

[rpeare]

you are populating the clientID, if the clientID is a bound field, when you populate it you are essentially creating a record, that record has to be undone before you do anything with the recordset (move away from the 'new' record by either selecting a new client or moving to an existing record under the same client)

[Paul H]

The me.undo got rid of the error, but I still am not showing the filtered records. The undo clears cboClientID and sets the filter to CCS = "".

ClientID (ccs) is the bound field in the Combo Box and the Primary Key in the Client table, but I am not adding a new client ID. Barring any progress I may try a subform. I really just want to display all existing records in the Housing Events table, the record source for the form, that have the particular Client ID selected from the combo box.

I'm calling it a night and checking back in the AM.

Adios amigos.

Paul

[aytee111]

NEVER bind a combobox, it will change the underlying data!

[Micron]

Never say never.
If you're using a form to create records, the value chosen in a combo could be a value list or the result of a select statement which you then want to populate a field with. It's one of the certain ways to ensure only acceptable choices make it into the table as opposed to using a free form text box (which would then need validation to comply with any restrictions).

Then there's the case where you want to display the values from an existing record using the same form in view-only mode. You lock or disable the combo.

[Paul H]

That's exactly what I am doing, using a query to populate a combo box, then using the bound value to filter a list. The ccs box on the edited form auto populates, I think, due to referential integrity in the relationships.

Anyway using undo does not help.

[rpeare]

can you supply sample version of your database