Open Form to single record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been using Access for some time with medium to advanced competency,
but I have not yet been able to find a good way yto accomplish this.

I would like to be able to open a form, filtered on one record via a unique
id, but have the form only display that record, i.e. not enable the user to
scroll through, or navigate to other records.

Is this something I would accomplish with a form property, or Open argument?

Thanks,

DEI
 
DEI said:
I have been using Access for some time with medium to advanced
competency, but I have not yet been able to find a good way yto
accomplish this.

I would like to be able to open a form, filtered on one record via a
unique id, but have the form only display that record, i.e. not
enable the user to scroll through, or navigate to other records.

Is this something I would accomplish with a form property, or Open
argument?
Click to expand...

Assuming that the ID of the particular record you want is not known at
design time, the most common practice is to specify it when you open the
form, via the WhereCondition argument of the DoCmd.OpenForm method. For
example,

DoCmd.OpenForm "MyForm", _
WhereCondition:="ID=" & lngID

That will open the form filtered by the criterion supplied by
WhereCondition. However, the user could remove the filter to see all
records, if the form's AllowFilters property is set to Yes (True). To
prevent that, you could set that property to No (False) at design time,
or you could set the property immediately after opening the form:

DoCmd.OpenForm "MyForm", _
WhereCondition:="ID=" & lngID

Forms!MyForm.AllowFilters = False
 
I would like to be able to open a form, filtered on one record via a
unique id, but have the form only display that record, i.e. not enable
the user to scroll through, or navigate to other records.
Click to expand...

The way I do this is to

(a) design the form in bound mode, but delete the recordsource property
before saving it for the "last" time.

(b) use the Form_Load event to call a function like

public function GetNewRecordID(ByRef PKValue as Long) As Integer
' returns 0 if the user cancelled,
' returns 1 if it's a valid recordID
' returns 2 if it user requests a new empty record
' (sometimes this function will create the new record and
' return the new RecordID instead)
'
' If I wasn't so lazy I'd create this as an Enum instead!

which displays some kind of search dialog or way of selecting a new
record by the users. Back to the main form, it's something like this:

Private sub Form_Load(Cancel as integer)

dim newRecord As Integer
dim newRecordID as Long

' ask the user for a new record to look at
newRecord = GetNewRecordID(newRecordID)
Select Case newRecord
Case 0 ' user cancelled
Cancel = True
Exit Sub

Case 1 ' valid ID, easy
' actually I usually modularised better than this...
me.RecordSource = "SELECT This, That, TheOther " & _
"FROM MyTable " & _
"WHERE PKValue = " & Format(newRecordID)
' this is necessary if renewing
Me.Requery

Cancel = False ' not strictly necessary, but it's a reminder

Case 2 ' new record; if you don't want to allocate it directly
' the below is air code; I don't usually do it this way
newRecordID = GetNextVacantRecordID()
me.DataEntry = True
me.GoToNewRecord
me!txtRecordID.Value = newRecordID

Cancel = False ' not strictly necessary, but it's a reminder

Case Else
' oops....
Msgbox globalSystemErrorMessage
Cancel = True

End Select

End Sub


and don't forget the cmdExit button:

private sub cmdExit_Click()
dim Cancel as Integer

' don't want to see the form in the background with the
' old data
Me.Visible = False

' load a new record into the form
Call Form_Load(Cancel)

If Cancel = True then
' user wants to go home, close down the form
DoCmd.Close acForm, me.Name

Else
' otherwise become visible again with the new data
me.Visible = True

End If

End sub


This way the user just sees a cycle of select record -- edit record and
can simply cancel out of the select record dialog when she is bored.

Hope that helps


Tim F
 
Thank you, that works great. But if I use the 'acNNew' argument with the
Docmd.Open method, the form opens to a new record, but all the other records
in the underlying table are available.

Is there any way around this? So that when I want to add a new record, no
other records are available?

Thanks again.
 
DEI said:
Thank you, that works great. But if I use the 'acNNew' argument with
the Docmd.Open method, the form opens to a new record, but all the
other records in the underlying table are available.

Is there any way around this? So that when I want to add a new
record, no other records are available?
Click to expand...

I don't know what you mean by the "acNew" (or "acNNew") argument.
There's no such argument defined for the OpenForm method. If you write
this:

DoCmd.OpenForm "MyForm", DataMode:=acFormAdd

.... the form will be opened in Data Entry mode, showing only a blank,
new record initially, and afterward showing only those records that have
been entered in this data entry session.
 
Back
Top