advice: Form with subform recordsets.

[Brian]

Can someone please tell me if I am going in the right direction with this...
Don't want to spend too much time if I am way off... Thanks for any advice
in advance.

I am trying to produce a database for card issues. As one of this database
should be accessable from three locations allowing users to enter the data I
have opted for an access project adp linked to SQL server at head office.
Although I have written some simple mdb databases this is my first time
using an adp & SQL.

I have created two tables:

tbl_customers:
cust_id, datestamp, title, forename, surname, address1, address2, district,
town, county, postcode, email, contactpref, contactnil, dob,
ageverification, comments

tbl_cardissues
cust_id, cardserial, carddesc, issuedate, startdate, expirydate, comments

I have created a one2many relationship between the two forms in the database
diagram.
I have created a form with a subform (initially bound fields to table...)
The form & subform have now got an ADO recordset to fill the fields.

As this is my first database frontend to SQL I am unsure if this is the way
to go... I obviously want to limit the data across the network to ensure the
speed is usable at remote sites, so am unsure if i should have used a stored
procedure?

The only code i have is (as I am using Access2k, this is the example i found
at ms which required MSDatashape). This appears to work well for th main
part of the form but when data is entered into the subform it fails and when
playing with the subform the CUST_ID was issuing a new id not related to the
main form...:

frm_customers:
----------------

Sub Form_Open(Cancel As Integer)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "MSDataShape"

'The code below uses a server name, user ID, and password.
'Be sure to use your actual server name, user ID, and password.
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
SOURCE=(local);DATABASE=wdcardsSQL;UID=sa;PWD=sapassword;"
.CursorLocation = adUseServer
.Open
End With

Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM tbl_customers"
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs
Me.UniqueTable = "tbl_customers"

End Sub

frm_cardissues:
----------------


Sub Form_Open(Cancel As Integer)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "MSDataShape"

'The code below uses a server name, user ID, and password.
'Be sure to use your actual server name, user ID, and password.
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
SOURCE=(local);DATABASE=wdcardsSQL;UID=sa;PWD=letmein;"
.CursorLocation = adUseServer
.Open
End With

Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM tbl_cardissues"
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs
Me.UniqueTable = "tbl_cardissues"

End Sub


Thanks for any advice offered

Brian
(e-mail address removed)

 

[Vadim Rapp]

Hello Brian:
You wrote on Fri, 6 Aug 2004 12:38:21 +0100:

B> Can someone please tell me if I am going in the right direction with
B> this... Don't want to spend too much time if I am way off... Thanks for
B> any advice in advance.

You did very well with the tables. But totally wrong with the forms.

You don't need to use any ADO objects. Access does it for you. It's the same
as you (hopefully) did not use DAO objects in MDB. All you have to do is to
specify the recordsource of the form, or datasource of the combobox - Access
will do the dirty work. With form/subform, specify parent and child fields
to link by - Access will do everything else.

In fact you should throw away _all_ the VBA code you posted: for example,
Access will do everything (and more) you programmed in
frm_cardissues/form_open if you specify (in form's designer) recordsource =
SELECT * FROM tbl_cardissues.


Vadim

 

[Brian]

Thanks for the advice... obviously over complicating it.
I initially had it working using bound fields direct to tables, although it
worked fine, i thought if i used a recordset on the form it may speed up if
a user accessed the data.

The form will be in data entry mode mostly, however there will be cases when
the user has to find a person in the database, so i have looked at the ado
recordset option and was about to look into a stored procedure...

I will be running the front end over a new vpn, but when i spoke to a sister
company who has a similar card scheme they had a mdb on the server & had
major problems with users accessing, so they obviouly want a copy of
whatever i produce, i don't know there network capabilities so was trying to
produce something that is not going to max any connections.

Brian

 

[Sirocco]

You state "when
playing with the subform the CUST_ID was issuing a new id not related to the
main form...:" This sounds like a "required field" issue. The fields
that link the main and sub forms should be required fields, so the subform
will display the matching info. The field(s) used to relate the main and
sub-forms, besides being required should also be unique (i.e. the
*combination* of values for these fields should be unique), and indexed to
allow faster loading, which can be VERY important if the database grows to a
large size.