[maxx_eclipse]

Afternoon all,

Let me explain what I am attempting to do here:

I have a database with a list of contacts, and with these list of contacts, I have also created an agenda. Inside this agenda table, I have a field called "Contact ID". "Contact ID" has a one-to-many relationship set up with the primary key in the contacts table, "ID". (refer to pic1.jpg) I intend for the database to automatically update the "Contact ID" based on what contact is currently selected.

For example, If I open a contact with ID #3 (refer to pic2.jpg), and then I open the Agenda form, the "Contact ID" will automatically update to the contact "ID". From here, I can add events and such that will automatically relate to that particular client.

My problem is I don't know how I should do this, whether I need to set a default value for "Contact ID", use a query, or use some sort of VB coding to get it done. I've already tried to use things like:

Private Form_Load()

[AGENDA].[Contact ID] = [CONTACTS].[ID]

and that didn't work. I assume I might need something like a TempVar or ActiveForm/ActiveDatasheet code to call for ID to be automatically updated?

Thanks for your help everyone, looking forward to your responses!

[maxx_eclipse]

I've gotten this far with some results:

Private Sub Form_Load()
On Error Resume Next
    
Dim ID2CID As Integer
    
    Application.SetOption "Confirm Action Queries", False
    ID2CID = Screen.ActiveControl
    MsgBox ID2CID
    
End Sub

The MsgBox displays the required ID, but now I'm having trouble telling the program that when the Agenda form is open, Variable "ID2CID" is set as [ContactID].

I tried to do the following:

Private Sub Agenda_Click()

    [ContactID] = ID2CID

End Sub

but nothing happens. But when I try:

Private Sub SUBFORM_AGENDA_Enter()

    [ContactID] = ID2CID

End Sub

I get the following error:

Run-time error '2465':

Address Book* (* my database name) can't find field 'l' referred to in your expression.

I fail to understand where VB is picking up the letter 'l' in my expression, because out of all letters of the alphabet, there is not a single letter 'l' in there. I confirmed this by checking Notepad with the character input, and it does say that the error is with the letter 'l'. Not capital 'i' or the number '1'...

Microsoft VB help gives me the associated help ID HV01202015, which says:

Keyword Not Found

The keyword you selected can't be found in Visual Basic Help. You may have misspelled the keyword, selected too much or too little text, or asked for help on a word that isn't a valid Visual Basic keyword.

The keyword you want help on may be contained within an object library that is not referenced. Make sure references are set to the appropriate object libraries for all objects used in your code.

The easiest way to get help on a specific keyword is to position the insertion point anywhere within the keyword and press F1. You don't have to select the keyword. In fact, if you select only a portion of the keyword, or more than a single word, Help won't find what you're looking for.

The Value property topic is displayed when you press F1 with the insertion point between the "a" and the "l" in the Value keyword as shown in the following example.

Worksheets(1).Range ("A2").value=3.14159

Any ideas on how I can set [ContactID] as "ID2CID"?

[maxx_eclipse]

Well, I've tried something new:

Private Sub Form_Load()

Dim ID2CID As Integer
    
    Application.SetOption "Confirm Action Queries", False
    ID2CID = Screen.ActiveControl
    Forms!SUBFORM_AGENDA.Controls!ContactID = ID2CID
    
End Sub

And I now get:

Run-time error '2450':

Address Book can't find the form 'SUBFORM_AGENDA' referred to in a macro expression or Visual Basic code.

It seems that VB can't set the property (or value, in this case) of a field because the form is actually not open. When I opened the form in the background, there was actually no problem in setting the value.

I will continue to post my developments and issues here for those who are interested in following, as trivial (or complicated) as this might be to some.

[OBP]

You are going about this completely the wrong way around and making it very difficult.
It is normal to have the Agenda table data as a Subform on a Mainform that has the Contact ID on it.
The 2 forms are Linked via Master/Child links so that any new record in the Subform is automatically generated with the Correct Contact ID and only those Records with that contact id will be displayed for that Contact.
If you insist on opening a separate form for the Agenda data's new Record you pass the Contact ID using the The OpenArgs function and VBA. Or use Vba to reference the opening Contact Form before it is closed (if you are closing it).

[maxx_eclipse]

...I honestly have no idea what's going on now.

My two problems I'm having now is this:

1) I can't get Access to automatically link the ID of the customer to the agenda "Contact ID" field, so that any new appointments/interactions/events related to a particular customer will have his/her ID copied to the agenda field. Everytime I add something, I have to manually input the contact ID, I can't get a relationship established to do it automatically.

2) I'm having general issues with the Agenda now. In my agenda form, I have the master form, which is the detailed form view, displaying the text boxes of all the fields, within the master form, I have a subform, which is supposed to be the datasheet view, so that when I select an entry in the datasheet (subform), the master form is updated to display the selected entry. I tried using the VB provided here on the MSDN help page (http://msdn.microsoft.com/en-us/libr...offtips_topic7), but VBA responds with "expected: expression".

I've been searching the internet for a solution to this problem, and it's been quite an irksome situation. Anyone know what I need to do?

[maxx_eclipse]

Well, part of the problem is solved:

In my state of confusion and frustration, I really didn't understand what your point was. I took a break, and when I came back, I had gone and editted the "Master Link Field" and "Child Link Field", to 'ID' and 'ContactID' respectively. And now it all falls into place!

Thanks OBP for "knocking" some sense in to me. Now I can move on!

But I'm still having one minor issue, when I select a record in the datasheet (subform), there is no dynamic update to display the same record in the master form. I suppose I need to have some "On Current" or "On Change" code to fix this? I suppose I could use a DoCmd.GoToRecord and a string to tell it which record to update to?

[OBP]

I posted a VBA example of what you want to do some time ago, I will see if I can find it.

[OBP]

Here it is
http://forums.techguy.org/business-a...-opened-3.html

[maxx_eclipse]

OBP, I had a look at the VBA, but I'm receiving the following error:

Run-time error code '3159':

Not a valid bookmark.

Any ideas what this means?

I'm told that the error originates here:

Me.Parent.Bookmark = Me.Bookmark

[OBP]

Sorry, I forgot to say that the Version that I posted does not use a split Form, it uses an actual Subform that works like a Split Form. That is required to overcome the problem with the Split Form.
You als need all of the Code in all of the sections in both Forms.

[maxx_eclipse]

I added all the code in all of the sections in both forms, but I still get the same error.

[OBP]

Did you create a separate Subform?
Does the database that I posted work when you open it?

[maxx_eclipse]

Now that you mention it, when I opened it, no data was displayed on any of the forms that I opened...

Could I be missing some VBA object?

Here's my latest list:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library (*)
Microsoft JET & Replication Objects 2.6 Library
Microsoft Office 12.0 Object Library
Microsoft Office 12.0 Access Database Engine Object Library (ACEDAO)
Microsoft Visual Studio 2008 Tools for Office Execution engine 9.0 Object Library

(*) I have version 6.0 of the ActiveX Data Objects Library, does it really matter which one I have referenced?

[OBP]

The data is not being displayed because of the Date Filter in the Query, it is set to >Date()-30 And <Date()+30, set it to >Date()-150 And <Date()+30 to see the data or remove the filter altogether.

[maxx_eclipse]

Okay, I made the change, and the data now shows up, but I noticed that the only way to have the information update is to click on the row selector (black-right arrow), and even then, when I select the data, the subform reformats itself, and half the normal view is cut off and the datasheet is "moved up".

Does this happen on your side?