Form Record Selection 1

[BabyPowder2u]

I would like to create a form which provides a cbobox which selects the record which fills the form based on a ValidationID.

It is basically the same form that the original record was entered thru, but now I want the form to open empty, and have the user select the record to view/update based on cboValidationID.

How would I accomplish this?

 

[Eupher]

Well, except for the part about opening the form to a blank record, what you want to do sounds remarkably like what you would get if you placed a combo on your form and used the "Find a record on my form based on the value in my combo" wizard.

If you absolutely must open to a blank record, I would leave the form unbound (i.e. nothing in the Record Source field on the form's property sheet). Then in the AfterUpdate event of the combo, create a SQL string with the value of the combo as the WHERE clause and make the string the Record Source of the form. Then in the form's OnClose event, set the Record Source back to a null string, so it's blank again the next time you open the form. Something like this:

Private Sub cboMyCombo_AfterUpdate()
Dim SQLStmt As String
If Not IsNull(Me!cboMyCombo) Then
    SQLStmt = "SELECT * FROM tblMyTable WHERE ValidationID = '" & Me!cboMyCombo & "'"
    Me.RecordSource = SQLStmt
End If
End Sub

Private Sub Form_Close()
    Me.RecordSource = vbNullString
End Sub

A possible complication is whether the *controls* on your form are bound (not the form itself). If they are bound, this will work fine, but when the form opens, since there is no record source, the bound controls will display "#Name?". If that's not to your liking, then you're left to not only set a record source for the form when the combo updates, but also set a control source for each control on the form.


HTH,

Ken S.

 

[Eupher]

Another way to do it would be to bind the form to your table/query, but set it to data entry mode so it opens to a blank record. Then in the combo's after update event, you could turn data entry mode off and apply a filter to get the selected record.

Ken S.

 

[BabyPowder2u]

Thanks Eupher,

By "apply a filter to get the selected record" how would I accomplish this?

 

[Eupher]

Take a look a the Filter and FilterOn properties of the form.

Ken S.

 

[BabyPowder2u]

Hello again,

I created the cbo using the "Find a record on my form based on the value in my combo" wizard. This works great.

However, I really do need the form to open to a blank form. If I open it in Add Mode (from switchboard manager I can only choose Add or Edit modes) then if I select something from the cbobox it doesn't do the lookup, it remains in add mode with the fields blank.

I am fairly new to all this. I don't know how to "turn data entry mode off"
and I looked at the filter & FilterOn properties of the form, but I really don't understand them, could you give me a little more direction, or let me know what info about the form I could give you to clarify your suggetions.

Thanks,

T

 

[Eupher]

Okay, since you want to always open the form in Data Entry mode, you will need to replace the code in the combo's AfterUpdate event procedure. Something like this should work (replace the fields in blue with YOUR fields):

Dim strMyData As String
Dim strMyFilter As String
strMyData = Me![[COLOR=blue]cmbMyCombo[/color]]
strMyFilter = "[[COLOR=blue]MyPKField[/color]] = '" & strMyData & "'"
Me.DataEntry = False
Me.Filter = strMyFilter
Me.FilterOn = True

Then in the form's Close or Unload event, you need to reset the DataEntry and Filter properties so the form will open in Data Entry mode next time:

Me.Filter = vbNullString
Me.FilterOn = False
Me.DataEntry = True

HTH,

Ken S.