Hi, I have a table of notes on a

[Bob500]

Hi,

I have a table of notes on a 'notes' form that I need to open from a command button. The notes need to be specific to the currently 'focused' primary key called ipcisID of the main form. I cannot link them up, I have tried using the wizard but it will only link to one of the primary key ID numbers,

The code on the onclick event of the command button is:

Private Sub opennotes_Click()
On Error GoTo Err_opennotes_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmdelaynotes"

stLinkCriteria = "[ipcisID]=" & "'" & Me![ipcisID] & "'"
docmd.Openform stDocName, , , stLinkCriteria

Exit_opennotes_Click:
Exit Sub

Err_opennotes_Click:
MsgBox Err.Description
Resume Exit_opennotes_Click

End Sub

Any ideas? Thx

 

[BillPower]

A string:
stLinkCriteria = "[ipcisID]= '" & Me![ipcisID] & "'"
A number:
stLinkCriteria = "[ipcisID]= " & Me![ipcisID]

 

[Bob500]

Hi Billpower,

I have amended the code to make sure that it allows for a string but I still can't get it to link to the primary key.

Have I set the relationship incorrectly on the tables, currently I have rererential integrity enabled with both cascades enabled. I am really stuck on this any ideas would be great

 

[TonyJollans]

Hi Bob500,

What do you mean when you say but it will only link to one of the primary key ID numbers?

What is the recordset for your notes form? What actually is the error/problem you are getting?

Enjoy,
Tony

 

[Bob500]

The notes form is just constructed from a table that has two fields:

ipcisID (the foreign key)
Notes (a memo field for notes)

The notes form is opened by clicking on a command button on the main form, which has the following relevent field:

ipcisID (the primary key)

So basically the corrct note on the notes form for the currently selected record on the main form is displayed.

I am using the code from the wizard (see first post) but it does not seem to be linking.

 

[PeteJohnston]

I don't think that cascade update/delete on the linked tables would make any difference. When you say "it will only link to one of the primary key ID numbers", do you mean that one of the tables has a unique key made up of multiple fields? If so you can amend the Access-generated opennotes_Click to be

stLinkCriteria = "[ipcisID]=" & "'" & ipcisID & "' AND [otherfield]='" & otherfield & "'"

This can be extended to include as many selection criteria as there are fields in the key.


PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[KenReay]

Hi

From your description:

ipcisID (the foreign key)
Notes (a memo field for notes)

The Notes table does not have a unique key, at least the column ipcisID is not a unique key on that table, there fore the code you ahev should present all notes records with teh relevant matching ipcisID, all you need in than case is a means to navigate through the 'set' of selected notes

Or am I missing something here ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bob500]

Um, There really isn't a navigation as such just a memo text box with some words in it.

For example you are currently viewing the data for ipcisID number BX1234, you click the goto notes button and the relevent notes for that ID number pop up in the notes form.

The notes table does have a unique key which is the foreign key of the main primary key which is what links the data, do I still need a primary key? The notes table is initially empty and is populated as is necessary with the ipcisID and then the notes that are entered

As you can tell, I am still stuck on this

 

[TonyJollans]

Hi Bob500,

It doesn't sound there is anything complicated about this at all, so either there is something you are not telling us or something we are misunderstanding.

To clarify:

You have two Tables, both with a unique Field (which may or may not be a key - it shouldn't make any difference) called ipcisID, and two stand-alone Forms (one based on each table).

From a display of your 'Main' record in its Form you push a button which (should) open your Notes Form and display your Notes record with the same ipcisID.

Now some questions:

What actually happens when you push your button the first time?
Do you close the Notes Form before returniong to the Main Form?
What actually happens when you push your button the second time?

What differences are there when the Notes record doesn't exist compared to when it does exist?

What do you see when you open the Notes Form independently of your Main Form?

Enjoy,
Tony

 

[KenReay]

Hi

Like TonyJollans I am puzzled, from what you say, it should be simple to do what I (think) you are trying to do.

Now I do not even understand why you have the notes in a seperate table, it appears theer is a one-one relationship between to two tables.

Can you explain some more?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bob500]

Good question, I have put the notes field on the end of the main table and it now works fine.

Apologies for being dumb, thanks to everyone for the help!