Data Entry Form

[edworboys]

I have a form (Prospect) with a subform (Document).

The result I am looking for is this:

The user selects a prospect which is made up of Prospect Name, Country,
Company and Prospect Type. They can then use the subform to add records
into the database with the above information already there.
At the moment I have four combo boxes in the Prospect part of the form.
The user can use the combo boxes to select a prospect. The document
subform has the "data entry" property enabled so the user enters
document names and locations.

The problem is that rather than inserting each document under prospect
names which already exist, Access is creating a new propsect (with the
same name but different ID) and filing them under that.

How can I use the combo boxes to navigate to a particular prospect
record (an add prospect feature is not required), and have access put
the documents inserted in the subform into this?

Here is a screenshot of the form if it will help:
http://img35.exs.cx/img35/9764/eg1.jpg

Thanks
Ed

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I suggest that you set up your form in 2 sections:

Header section - search section.

Detail section - Prospect record section and Document subform (keep your
current set up).

In the Header section put the Combo Boxes that define the Prospect
search. These Combo Boxes are UNBOUND controls (that means their
ControlSource property is empty) - be sure these controls have different
names than those controls in the Detail section. Use the same RowSource
values that you are using for the ComboBoxes in the Detail section.
Place a CommandButton in the Header section that initiates the search
after all the criteria has been selected/entered in the search Combo
Boxes.

The CommandButton OnClick event is set up to change the WHERE clause of
an SQL statement. The WHERE clause is created by reading the values of
the header section ComboBoxes. E.g.:

Private Sub cmdSearch_Click ()

const SQL = "SELECT * FROM table_name WHERE "

dim strWhere as string

If Not IsNull(Me!cboCo untry) Then
' value in Country ComboBox is string & requires delimiters
strWhere = "Country = '" & Me!cboCountry & "' "
End If

If Not IsNull(Me!cboPr ospectType) Then
If Len(strWhere)>0 Then strWhere = strWhere & " AND "
' value in ProspectType ComboBox is number
strWhere = "ProspectTy pe = " & Me!cboProspectT ype
End If

' ... other search criteria controls are parsed

' Put the new SQL string in the form's RecordSource property
Me.RecordSource = SQL & strWhere

' You don't need to requery the form: putting a value in the
' RecordSource propery automatically requeries the form.

End Sub

You should put in error traps/handler code and other criteria controls
as needed.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUhokIechKq OuFEgEQLcCgCgoR LK2sCnrVFGkj0UR IB56Y/FLfQAnjuL
LG/0Kc8dBx0HVtvAPX R1ZgLT
=GT3s
-----END PGP SIGNATURE-----
ed*******@gmail .com wrote:

I have a form (Prospect) with a subform (Document).

The result I am looking for is this:

The user selects a prospect which is made up of Prospect Name, Country,
Company and Prospect Type. They can then use the subform to add records
into the database with the above information already there.
At the moment I have four combo boxes in the Prospect part of the form.
The user can use the combo boxes to select a prospect. The document
subform has the "data entry" property enabled so the user enters
document names and locations.

The problem is that rather than inserting each document under prospect
names which already exist, Access is creating a new propsect (with the
same name but different ID) and filing them under that.

How can I use the combo boxes to navigate to a particular prospect
record (an add prospect feature is not required), and have access put
the documents inserted in the subform into this?

Here is a screenshot of the form if it will help:
http://img35.exs.cx/img35/9764/eg1.jpg