access newbie

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

hi

I'm having a little trouble putting something together in MS Access

I trying to create a problem recording db. there is a 'problem' table that
should hold the problem number, date opened, owner etc. etc., that part
work, no problems there. the table has the following fields

pr_index (autonumber - primary key)
pr_number (unique internally assign number for this problem)
pr_owner (who the problem is assigned to)
pr_date (date pr opened)

what I'm now trying to add in, is a second table to contain notes entered
about each problem. I have created a 'notes' table with the fields

note_id (autonumber - primary key)
pr_number (foreign key that links up a with pr_number in the main problems
table)
notes_text (255 char text area)
notes_date (stores the date and time of the note entry)

I created a form, and can sucessfully populate the main table with a
pr_number, owner, and date. what I'm now trying to do is add a text entry
box onto the form, that is bound to the notes table, not the 'problem' table

what I'm trying to get to happen, is that when the record is submitted, it
takes the pr_number from the form (which is actually bound to the
'problems.pr_number' table/field), along with the text in the unbound
'note_text' field (don't seem to be able to bind to multiple table on a form
?) and populate the 'notes' table

I created a button with the following event code

Private Sub add_button_Click()
On Error GoTo Err_add_button_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenQuery "insert_initial_notes"
DoCmd.GoToRecord , , acNewRec

Exit_add_button_Click:
Exit Sub

and then created an 'insert_initial_notes' append query with the following

INSERT INTO notes (pr_number, notes_text)
VALUES (Forms!add_new_problem!pr_number, Forms!add_new_problem!notes_text);


it seems work ok if I run the query in isolation (it prompts for a pr_number
and notes_text), but not when I run it from the form

is there a better way I should be doing this ?

thanks

_scott
 
Scott

Access provides a form/subform mechanism that will automatically pick up
your "parent" table's ID. I believe you are running into a problem because
you are not using the parent table's ID.

From your description, you've selected something other that the tblProblem's
primary key field as your foreign key for the tblNote row. To take full
advantage of what Access can help you with, you need to be storing the
primary key value of the tblProblem row which is the "parent" of your note.
 
pr_index (autonumber - primary key)
pr_number (unique internally assign number for this problem)
pr_owner (who the problem is assigned to)
pr_date (date pr opened)
Click to expand...

Just to add to Jeff's comment - if you have a unique, stable, reliable
unique internally assigned number (pr_number), it is probably a bad
idea to assign a formally redundant autonumber pr_index. I'd suggest
just making pr_number the Primary Key of this table, dropping
pr_index, and use pr_number as your link.
 
thanks

I have amended the 'problems' table so that the primary key is now the
pr_number, and removed the autoincrement number field, but I'm still having
trouble

if I was writing this as a web page/sql backend, I would generate x2 sets of
SQL insert statements, one into the problem table and one into the notes
table, utilising the pr_number entered

but Access doesn't seem to permit this (it keeps changing my SQL statements
automatically behind the scenes??), is Access capable of inserting into x2
tables at the same time from one entry form ?

thanks

_scott
 
cracked it :o)

moved the whole thing up into some VBA code

pr_number = Forms!add_new_problem!pr_number
notes_text = Forms!add_new_problem!notes_text
DoCmd.RunSQL "insert into notes(pr_number, notes_text) VALUES (pr_number,
notes_text)"

thanks for all help and comments

_scott
 
if I was writing this as a web page/sql backend, I would generate x2 sets of
SQL insert statements, one into the problem table and one into the notes
table, utilising the pr_number entered

but Access doesn't seem to permit this (it keeps changing my SQL statements
automatically behind the scenes??), is Access capable of inserting into x2
tables at the same time from one entry form ?
Click to expand...

Typically this would be done using a Form for the main table, a
Subform for the notes table, and the linking field as the master/child
link field. No code needed.

Glad you got it working, even if doing so in a somewhat different
manner!
 
Back
Top