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

[Pat Hartman]

as you would need a few more lines to add to the AfterUpdate event of the combo to do the search, add a control and modify the record source of the form and probably the biggest impact would be that in some cases the combo "search" could interfere with some custom search code (some people use filters, some change the recordsource, search as you type, etc.).

Perhaps you just didn't understand the explanation Only the one line of code is required to do what I described. The query contains the criteria. the code simply reruns the form's RecordSource query to force it to select the requested value.

@Sampoline, since you seem to be happy with your current solution, I won't go into details. But to make the form open to a specific record, you would pass the ID in the OpenArgs and use code in the form's Open event to populate the combo.

 

[bastanu]

Hi Pat, what would make you think I didn't understand what you suggested? My point was that to make it work the way you suggested you would have to alter the form's recordsource to return only one record at the time which will interfere with certain custom searches\functionality (you loose the ability to filter the form, to use the built in record navigation buttons to move from record to record which could be important to certain users). You would also have to add lines of code to the AfterUpdate event of the new unbound combo box to do the search (at the least a Me.Requery but I would expect one would want some data entry validation, error trapping, etc.). So it is not a one-liner solution. To open the form with what I suggested is also only one line, you just pass the ID in a different spot.

Just my view of course....

Cheers,
Vlad

 

[Pat Hartman]

The vast majority of my apps (even for small companies) use SQL Server as the BE. That means that forms are NEVER filtered. All the filtering is done using unbound criteria fields or specialized search forms. A major point of SQL Server is to get the server to do the heavy lifting. If you bind your forms to tables or queries without criteria, you make the form act like a straw. The form opens as soon as there's a record to populate it and Access just sits there sucking down data from the server until every single record has been downloaded. Very poor practice if you want efficiency.

Of course if the BE is Jet/ACE it doesn't matter much. My method works regardless of the BE, the filter method works well ONLY for Jet/ACE and is why when people do a straight conversion from Jet/ACE to SQL Server they are dumbfounded by how slow their app became and find themselves with major rework to get acceptable performance

you loose the ability to filter the form, to use the built in record navigation buttons to move from record to record which could be important to certain user

Users don't need to browse through thousands of records. They might need criteria to select a set of records though or to find a particular record. So I always give them search options but NEVER, EVER bind a form to a table or query without criteria. So, yes, my main forms for the most part, bring back single records. A user can't actually work on more than one record at a time so I've never found it to be a problem. It also means that I can convert any app I build to a different BE in under an hour. And the time is mostly for testing. No code needs changing. You want DB2? Oracle? Sybase? Progressive? ACE? NP, the app will work with all of them.
.

 

[bastanu]

Thanks for the explanation Pat, I kinda' know all that as I have done a lot of work with Access front-ends and SQL Server, MySQL, Oracle for quite a few Canadian colleges and universities. But there are certain adbvantages for being able to navigate from one record to the next using the built-in record navigation, for axample in a db looking at some sort of sequential data like appointments or notes. And I also know you can program your own buttons to simulte this with a single record form like yours, my point was that your soultion was not really a one-liner (as you needed code for the combo) and maybe not the ultimate "one-size fits all" solution.

But as the OP now should have all the facts maybe I'll just leave up to them to choose their prefered methid

All the best and I hope I dit not bothered you!

Cheers,
Vlad

 

[Pat Hartman]

You are entitled to build your apps however you see fit. As long as your forms are bound to tables that don't have more than a few thousand rows, and your LAN is fast, technique doesn't matter. If you ever used the SQL Server tools to watch the traffic between Access and SQL Server, you would be flabbergasted by how chatty Access is. I prefer to keep the chatter to the absolute minimum.

 

[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,

 

[Pat Hartman]

The unbound controls are used ONLY for searching. The bound controls should NOT have been modified and should continue to work.

 

[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.