cursor stops at first/last field....

[Guest]

I want to cretae a form which will only allow one record to be added. I have
noticed an an option under Tools|Options|Keyboard called Cursor stops at
first/last field.

Presumably this option only applies to the local copy of Access and not the
database..?

Is there any way I can set this option for the database, so that this option
is set to true whenever anybody is running my access application?

(Alternatively, can anyone suggest a reliable way of creating a form which
will only allow a single record to be added)

Thanks

Nick

 

[Albert D.Kallal]

No, just use the forms settings in the other tab

Set the cycle property to "current record"

next, in the data tab, change the allow additions to "no" (you read this
right!!!).

I would also turn of the record navigation buttons..since you don't need
them either!

Ok, now, to add only ONE record, simply go:

docmd.OpenForm "YourForm",,,,acFormAdd

Note that while the form is set to NOT allow additions, the above command
actually overrides this setting, BUT ONLY FOR ONE record, which turns out to
exactly what we need.

 

[Guest]

That sound good. The only problem I have, is that the form is actually a
subform..

Basically, the main form has a list box from which the user can select a
product. The subform then displays the details for that product.. (Not using
a subform link, but by setting the subform's recordsource to SQL ststement)

I have created a 'new' button which currently changes the subform to
dataentry, but I want to force the user to press the 'new' button again if
they want add a second product...

Your will work much better, but is there a way of doing it without the
openform method?

Thanks

 

[Albert D.Kallal]

That sound good. The only problem I have, is that the form is actually a
subform..

Ok...just one of those details that was important!

The simply solution here is of course to have the sub-form setting to NOT
allow new additions. This has the nice added benefit of the sub-form NOT
displaying a blank extra line "ready" for data entry as a normal sub-form
would have.

I have created a 'new' button which currently changes the subform to
dataentry, but I want to force the user to press the 'new' button again if
they want add a second product...

I would not play with the data entry setting. Simply put some code behind
your new button that:

Adds a new record.
requery the sub-form to display the new record.
move your cursor to the new record just added.

The above means you have ONE approach to adding records (you don't want to
code a solution that has something different to add the first record, and
then some code to add additional records. Lets get ONE process here that
works for all cases.

It is not quite clear how the listbox gets set, and if we are view some type
of relational data (one to many). At any rate, the button code to add a new
record would be: (we are assuming this button code is in the main
form..right??).

Dim rstData As DAO.Recordset
Dim lngid As Long

Set rstData = Me.contactChild_Subform1.Form.Recordset

rstData.AddNew

rstData!contact_id = Me!ContactID
' above line is NOT needed if the data is not relatonal

' above would be the code that sets the master link/child
' since we are adding the record in code, access will
' not set this for us

lngid = rstData!ID
rstData.Update

' now, requery sub-form
Me.contactChild_Subform1.Requery

' move cursor to sub-form
Me.contactChild_Subform1.SetFocus

' move cursor to correct reocrd (new we just added)
Set rstData = Me.contactChild_Subform1.Form.RecordsetClone
rstData.FindFirst "id = " & lngid
Me.contactChild_Subform1.Form.Bookmark = rstData.Bookmark

rstData.Close
Set rstData = Nothing