Solved How to get a startup form to open with blank fields but once in the database it open the specific record?

[MCCDOM]

When you want a form to work to both an add and an edit (as we normally do), you need a search feature on the form that uses unbound controls. Either combos or text boxes. Then the RecordSource for the form will be a query that uses the form's unbound control as criteria.

Select ... From .. Where SomeField = Forms!yourform!cboSomeField

Then in the AfterUpdate event of the unbound combo/textbox, use:

Me.Requery

The form will always open empty since the criteria field will be null.

Thanks @Pat Hartman this worked for me with trying to open the form (frmDeviceDetails) as blank but then using a unbound combobox (cboDeviceSearch) to search records and have the fields populated accordingly. However this has now broken the functionality of clicking on the DeviceID on another form (frmStaffDetails) and have it open the frmDeviceDetails with the related information to the DeviceID, that I clicked on in frmStaffDetails.

If I remove the criteria [Forms]![DeviceDetails]![cboDeviceSearch] from the query that is the Record Source for frmDeviceDetails, then the function of clicking on the DeviceID loads the form with the relevant details but then the combobox no longer works at filtering.

How can I have both functions work in harmony, please?

 

[bastanu]

My point exactly that I was trying to make in post 22 and 24....
Please post the code you have in the frmStaffDetails form, it should be changed to populate the combo on the frmDeviceDetails form and then call it's AfterUpdate event (you will need to change it from Private to Public).
Cheers,

 

[bastanu]

The problem is the form now a has recordsource returning only one record, so MCCDOM's old code to open the form at a specific record is failing (haven't seen it yet but probably using Docmd.FIndRecord or recordset findfirst+ bookmark or similar methods), nothing to do with bound controls.

Cheers,

 

[MCCDOM]

Hi there, thank you for your replies. Sorry I missed out the part that the clickable DeviceID on frmStaffDetails was actually a subform sbfDevices. The code used for the clickable DeviceID is as follows:

'------------------------------------------------------------
' txtDeviceID_Click
'
'------------------------------------------------------------
Private Sub txtDeviceID_Click()
On Error GoTo txtDeviceID_Click_Err

Me.Dirty = False

DoCmd.OpenForm "DeviceDetails", _
    WhereCondition:="[DeviceID] = " & Me.DeviceID, _
    WindowMode:=acDialog, _
    OpenArgs:=(Me.DeviceID)

txtDeviceID_Click_Exit:
    Exit Sub

txtDeviceID_Click_Err:
    MsgBox Error$
    Resume txtDeviceID_Click_Exit

End Sub

Combobox is unbound with Row Source: SELECT tblDevices.DeviceName FROM tblDevices ORDER BY tblDevices.DeviceName;

The AfterUpdate code for the combobox is:

Private Sub cboDeviceSearch_AfterUpdate()
    
    Me.Requery
    
End Sub

Above is the Record Source for frmDeviceDetails

I hope this information helps and I look forward to your response.

Many thanks,

Dom

 

[bastanu]

Hi Dom,

Looks like the actual form name is DeviceDetails not frmDeviceDetails as you mention your your posts here so lets keep that in mind.

There are few issues with your setup. And you are not showing us the code you have in the open event of the DeviceDetails that makes use the OpenArgs argument (DeviceID) that is being passed in the Click event of the subform.

First of all I recommend you change your combo box to have two columns: DeviceID and DeviceName. The row source should be:
SELECT tblDevices.DeviceID, tblDevices.DeviceName FROM tblDevices ORDER BY tblDevices.DeviceName. Make sure you you set the Column Count property to 2, Column Widths to 0";3" (or whatever you need to display the device names) and the Bound Column to 1.

Next you need to adjust the form's recordsource by moving the reference to the combo from the DeviceName field to the DeviceID field.

Now the combo will allow you to still search by device name but it will be bound to the device id.

And finally update the code in the txtDeviceId_click on the subform to this:

'------------------------------------------------------------
' txtDeviceID_Click
'
'------------------------------------------------------------
Private Sub txtDeviceID_Click()
On Error GoTo txtDeviceID_Click_Err

Me.Dirty = False

DMe.Dirty = False

DoCmd.OpenForm "DeviceDetails", _
    WhereCondition:="[DeviceID] = " & Me.DeviceID, _
    WindowMode:=acDialog,' don't pass the OpenArgs as the form's recordset depends on the combo

Forms!DeviceDetails.cboDeviceSearch=Me.DeviceID 'we populate the combo with the current DeviceID
Call Form_DeviceDetails.cboDeviceSearch_AfterUpdate 'You need to change this procedure from Private to Public

txtDeviceID_Click_Exit:
    Exit Sub

txtDeviceID_Click_Err:
    MsgBox Error$
    Resume txtDeviceID_Click_Exit

End Sub

Cheers,

 

[MCCDOM]

Hi Vald,

Thank you for your response.

Looks like the actual form name is DeviceDetails not frmDeviceDetails as you mention your your posts here so lets keep that in mind.

Yeah sorry I just typed the from name like that to provide clarity of object being talked about for anybody reading.

There are few issues with your setup. And you are not showing us the code you have in the open event of the DeviceDetails that makes use the OpenArgs argument (DeviceID) that is being passed in the Click event of the subform.

That's because I don't have anything in the Open Event of the DeviceDetails form. What should I have in this event please?

First of all I recommend you change your combo box to have two columns: DeviceID and DeviceName. The row source should be:
SELECT tblDevices.DeviceID, tblDevices.DeviceName FROM tblDevices ORDER BY tblDevices.DeviceName. Make sure you you set the Column Count property to 2, Column Widths to 0";3" (or whatever you need to display the device names) and the Bound Column to 1.

Done.

Next you need to adjust the form's recordsource by moving the reference to the combo from the DeviceName field to the DeviceID field.

I presume this means swapping the criteria I had in DeviceName to DeviceID like below:

And finally update the code in the txtDeviceId_click on the subform to this:

I copied and pasted your code into the txtDeviceID_Click event but got a few errors. The first seemed to be caused by the comma at the end of acDialog. I presume this is alright to remove as it cured the Compiler Error: Expected: named parameter. Second issue is the DMe.Dirty, this through up a message when I tried clicking on the DeviceID in the subform "Object required". If I remove that bit of code the form opens after clicking on the Device ID but I still get a blank form. I'm guessing this is down to the earlier point of me not having a Open Event for the OpenArgs argument (DeviceID).

I look forward to your reply.

Many thanks,

Dom

 

[arnelgp]

what is the RowSource of combobox cboDeviceSearch?
if it Include MakeID as the First Column, then
you should put the Criteria to MakeID field.

 

[MCCDOM]

Hi @arnelgp thanks for your reply.

The Row Source of cboDeviceSearch is SELECT tblDevices.DeviceID, tblDevices.DeviceName FROM tblDevices ORDER BY tblDevices.DeviceName;

 

[arnelgp]

ok, what is the number for the "Bound Column" if 1, then you're criteria is correct.
if 2, then you need to put the criteria to DeviceName.

 

[MCCDOM]

Yeah the bound column of cboDeviceSearch is set to 1.