Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Programming & Database problem in MS ACCESS 97

I have two tables.  PACC and JACC for Primary account holder and joint account holder.  There is a 1:1 relationship.  The PACC table primary key is ACCNUM and the JACC Table has PACC as a Foreign Key with an Autonumber index. I have two forms: Primary Account Information and Joint Account Information. I do not have any problem writing to the PACC table from the Primary Account Information Form.

Now, I have a button on the Primary Account form that displays the Joint account form.  I want 3 different text boxes to display the ACCNUM, FNAME, LNAME from the PACC table on the JOINT Account Form, then I want to enter the Joint Account information into the JACC table from the JOINT Account form.  

I have attached the code that writes to the Primary Account table:  Becareful of the comments.  You can see what I was trying to do with a SQL statement.

If more info is needed email at spzbiegien@hotmail.com

Private Sub CmdEnter_Click()
Dim dbs As Database
Dim rs As Recordset
Dim sSQL As String
Dim ACNUM As Control

'Open the current database
Set dbs = CurrentDb
Set ACNUM = [Forms]![FRMMAIN]![ACCNUM]
'Open the current recordset

'sSQL = "SELECT tblTEST.ACCNUM, tblJTEST.ACCNUM, tblTEST.ACCSTATUS, tblTEST.ACCLEAVESTATUS, tblTEST.ACCSOCIAL, tblTEST.ACCFNAME, tblTEST.ACCMI, tblTEST.ACCLNAME, tblTEST.ACCADDRESS, tblTEST.ACCCITY, tblTEST.ACCSTATE, tblTEST.ACCZIP" FROM tblTEST INNER JOIN tblJTEST ON tblTEST.ACCNUM = tblJTEST.ACCNUM;"
'Set rs = dbs.OpenRecordset("SELECT tblTEST.ACCNUM, tblJTEST.ACCNUM, tblTEST.ACCSTATUS, tblTEST.ACCLEAVESTATUS, tblTEST.ACCSOCIAL, tblTEST.ACCFNAME, tblTEST.ACCMI, tblTEST.ACCLNAME, tblTEST.ACCADDRESS, tblTEST.ACCCITY, tblTEST.ACCSTATE, tblTEST.ACCZIP FROM(tblTEST INNER JOIN tblJTEST ON tblTEST.ACCNUM = tblJTEST.ACCNUM)")

'Set rs = dbs.OpenRecordset(sSQL)
Set rs = dbs.OpenRecordset("tblTEST")

With rs
.AddNew
    !ACCNUM = [Forms]![FRMMAIN]![ACCNUM]
    !ACCSTATUS = [Forms]![FRMMAIN]![ACCSTATUS]
    !ACCLEAVESTATUS = [Forms]![FRMMAIN]![ACCLEAVESTATUS]
    !ACCSOCIAL = [Forms]![FRMMAIN]![ACCSOCIAL]
    !ACCFNAME = UCase([Forms]![FRMMAIN]![ACCFNAME])
    !ACCMI = UCase([Forms]![FRMMAIN]![ACCMI])
    !ACCLNAME = UCase([Forms]![FRMMAIN]![ACCLNAME])
    !ACCADDRESS = [Forms]![FRMMAIN]![ACCADDRESS]
    !ACCCITY = UCase([Forms]![FRMMAIN]![ACCCITY])
    !ACCSTATE = UCase([Forms]![FRMMAIN]![ACCSTATE])
    !ACCZIP = [Forms]![FRMMAIN]![ACCZIP]
 .Update
 .Bookmark = rs.LastModified
 End With
 rs.Close
 
 'initialize the form
 Call INITIALIZE
 ' SetFocus to text box.
 ACNUM.SetFocus
End Sub
0
spzbiegien
Asked:
spzbiegien
1 Solution
 
nathanielIT ConsultantCommented:
pls. check your email
0
 
walterecookCommented:
EE Questions should not be handled by email.  If more info is needed, please post it.
http://www.experts-exchange.com/Databases/MS_Access/help.jsp#hi99

Walt
0
 
nico5038Commented:
Hmm, I guess we have a design issue here.
When you have a joint account holder table, only data that differs from the Primary account holder should be stored.

Personally I would use a Primary account holder mainform and a linked joint account holder subform.
This way the Foreign key of the joint account holder will be filled automatically when there's no joint account holder yet and when there's already one then access will automatically show the linked joint account holder.

Getting the idea ?

Nic;o)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now