# Solved: MS Access: Automatically determine the value of one field from an ID autonumb



## maxx_eclipse (May 29, 2007)

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 (May 29, 2007)

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.
> 
> ...


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


----------



## maxx_eclipse (May 29, 2007)

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 (Mar 8, 2005)

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 (May 29, 2007)

...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/library/aa163981(office.10).aspx#odc_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 (May 29, 2007)

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 (Mar 8, 2005)

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


----------



## OBP (Mar 8, 2005)

Here it is
http://forums.techguy.org/business-applications/870225-ms-access-split-form-opened-3.html


----------



## maxx_eclipse (May 29, 2007)

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 (Mar 8, 2005)

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 (May 29, 2007)

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


----------



## OBP (Mar 8, 2005)

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


----------



## maxx_eclipse (May 29, 2007)

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 (*)
> ...


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


----------



## OBP (Mar 8, 2005)

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 (May 29, 2007)

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?


----------



## OBP (Mar 8, 2005)

Not when there is only 4 records.
But you should be able to use the Sidebar to move around the records.


----------



## maxx_eclipse (May 29, 2007)

Okay, I fixed the initial problem of the repositioned form, apparently the form size in design view caused the datasheet to be centred, forcing the master view to be moved up.

I'll try adapting the VBA code again and see how it goes. I'll keep you informed.


----------



## maxx_eclipse (May 29, 2007)

OBP, I copied all the code related to the Form_Click() event for frmECS and sbfrmECS, and I changed all of the variables in my database to the respective forms, but VBA is telling me that:

Run-time error code '3159':

Not a valid bookmark, and then it highlights the following line.


```
Me.Parent.Bookmark = Me.Bookmark
```
 from the sbfrmECS table

As far as I'm aware, I changed everything correctly and have gone through it a few times now.

But come to think of it, I think I know what the issue is... in the database you sent me, your split form is just a split form, with a main form and the subform (datasheet). In my case, it's a split form AS A subform... I think that Me.Parent.Bookmark has to be changed to something like Me.Subform.SUBFORM_AGENDA.Bookmark... i'll tinker around and see what I get.

I've included a screen print of the actual agenda splitform as a subform.

I'll keep you posted.


----------



## OBP (Mar 8, 2005)

I would make the Subform a normal Subform based on the Table or a query, avoiding the Split form all together. I don't think a split form has it's own recordset like a subform does.


----------



## maxx_eclipse (May 29, 2007)

I'll try tinkering around with the code and let you know how it goes.


----------



## maxx_eclipse (May 29, 2007)

OBP, I found this page, which looks useful. If I can understand what is exactly done, and which line of code I need to use, I should be able to get the dynamic update working.

http://www.mvps.org/access/forms/frm0031.htm


----------



## OBP (Mar 8, 2005)

If you set your Form & Sub Form up the same way that I have in my example the Code that provided should work for you as well.


----------



## maxx_eclipse (May 29, 2007)

Well, if you refer to my Post #18, you'll see that I'm returned an error. However, I hypothesised why it occurs:

The code for the subform states that Me.Parent.Bookmark = Me.Bookmark

However, because the subforms operate through the main form, the subform2 uses Me.Bookmark (as it has the focus), but since subform1 is RUN THROUGH the mainform, I think I need to change the coding to:

Me![Subform1].Form.Bookmark = Me.Bookmark

I'm still yet to try this out, I'm leaving it till tomorrow to try it out and see how it goes. It's only a hypothesis, I'm only assuming that it could be the solution.


----------



## OBP (Mar 8, 2005)

I don't think that will work unless you are trying to set Subform1's bookmark to the Mainform Bookmark.
How come you have 2 Subforms?
Is Subform2 on Subform1 or the Mainform?
Which Forms are you trying to align the Bookmarks on?
Can you post an Example?


----------



## maxx_eclipse (May 29, 2007)

Subform2 is on Subform1 which is on the Mainform

I've uploaded the database. There is one contact entry, and 2 sample agenda entries.


----------



## OBP (Mar 8, 2005)

OK, so I have looked at your Tabbed Mainform.
The Subform_Agenda and Subform_Agenda_Datasheet is working as it should, so what isn't working?


----------



## maxx_eclipse (May 29, 2007)

OBP said:


> OK, so I have looked at your Tabbed Mainform.
> The Subform_Agenda and Subform_Agenda_Datasheet is working as it should, so what isn't working?


But do the subforms work in the mainform? Or did you look at them seperately? Because on my end, the Subform_Agenda and Subform_Agenda_Datasheet DON'T work in the main form.

Please clarify.


----------



## maxx_eclipse (May 29, 2007)

OBP,

I had a look at the database you did here (http://forums.techguy.org/6992705-post20.html), which is exactly the situation that I have:

Main Form
> SubForm1
>> SubForm2

And when I copied the code over to my database, I still received the same code issue in the subform2 coding, with "not a valid bookmark" problem. At first, I thought it had to with the fact that my form is pop-up. So as I changed your database, I noticed that it didn't make a difference.

HOWEVER, I've come to propose one final possibility (otherwise I've run out of ideas). I believe the code error is encountered because the "SubForm1" has a master link to the "Main Form", while the database you provided doesn't have any master link with the "Main Form"; your "SubForm1" just sits within the tabbed-form.

So to summarise, this is the difference:

Your sample database (from the post linked above) follows:

Main Form (unbound, not related to any tables) 
> SubForm1 
>> SubForm2

My database follows:

Main Form (BOUND by the contact information)
> SubForm1
>> SubForm2

...I'm convinced that the "Me.Parent.Bookmark" from SubForm2 conflicts with the 'Master Link' of SubForm1, and doesn't allow the code to execute. So is there anyway in trapping the error and forcing it to continue?

I am also wondering if I should ask people at the Microsoft Discussion Group to see if they could look into this and maybe see if it's a something that can be corrected as a hotfix (IF my hypothesis is to be true).


----------



## OBP (Mar 8, 2005)

It is working on the Mainform.
What it can't do is select the second Mainform Record in the 1st Subform as that is not possible as it is not listed due to the Master/Child Link. So the subforms can't control the Mainform Recordset.
But the 2 Subform VBA Links work exactly as expected.


----------



## maxx_eclipse (May 29, 2007)

Hmm...for whatever reason, it's not working on my end through the mainform. Did you adjust any properties when you had a look?

Also, if I were to change the format from:

Mainform
> SubForm1
>> SubForm2

To:

MainForm
> SubForm1
> SubForm2

Would something like that work? I believe something similar was done here:

http://www.mdbmakers.com/forums/showthread.php?t=23297


----------



## OBP (Mar 8, 2005)

My problem is that I can't see or reproduce what is "Not Working" at your end, so I don't know how to advise you.
When I open the Mainform and select a record in Subform1 the correct record in Subform2 is also selected. If I select a Record in Subform2 the correct record is also selected in Subform1.
If the database that I attached doesn't do that in Access 2007 then obviously Access 2007 has yet another problem.
I am sure that it worked for the original Poster that helped before, at least they didn't come back and say that it didn't.


----------



## maxx_eclipse (May 29, 2007)

Hmm... So it seems that I have a problem with Access. Do you think this is a case of something gone corrupt with the program that's not allowing it to work properly? Or possibly something that has been omitted from Access 2003?

I found this page, which shows some bugs and what's broken/missing from Access 2007, but I couldn't seem to locate anything that might suggest a problem. (http://allenbrowne.com/Access2007.html#Bugs)

Do you know anyone else who might have Access 2007? I would like to know if whether this is an isolated case, or whether it's a generic problem with Access 2007, so then that way I can report a bug.

I've also uploaded the ACCDB version of the database so that others may have a look at that one.


----------



## maxx_eclipse (May 29, 2007)

...I believe that my database might possibly be corrupted. I did some searching around for Error 3159, and I came across these 2 pages:

http://www.pcreview.co.uk/forums/thread-1068203.php
http://www.pcreview.co.uk/forums/thread-1076481.php

Both seem to point to a possible database corruption that isn't allowing for the database to work properly... Come to think of it, I just remembered that in the past few weeks, my database has not been compiling when I ask it to.


----------



## OBP (Mar 8, 2005)

I think Computerman29642 has Access 2007, so you could Private Mail him and ask.
This appears to be his last Thread
http://forums.techguy.org/dos-pda-other/897350-solved-batch-file-explanation.html


----------



## maxx_eclipse (May 29, 2007)

By the way OBP,

I posted something on the Microsoft discussion forum, and interestingly, one user replied with this message:



> I would rather try to find why it works in 2003, here what access help
> states:
> "However, you cannot use bookmarks from different Recordset objects
> interchangeably, even if they were created from the same source or command."


Page: Working in Access 2003 but not Access 2007


----------



## turbodante (Dec 19, 2008)

The file posted in #32 works for me, in that the ID on the Contact Details tab corresponds to that on Agenda.

Although, poking around it - i'm getting not-enough memory issues and problems with record source wotnots, but that last one could be to do with deleted tables.


----------



## OBP (Mar 8, 2005)

Turbodante, Converted Access 2007 forms do give those kinds of errors, sometimes you have to just delete the form and start again.


----------



## maxx_eclipse (May 29, 2007)

OBP, I received a reply from computerman29642, and he said that he'll have a look at the database during the weekend, so don't expect any activity on this post for another 3-4 days (unless you, me or others post something on it).

By the way, just as a reminder, I also have the discussion on the Microsoft Programming forum HERE:

Working in Access 2003 but not Access 2007


----------



## maxx_eclipse (May 29, 2007)

Focusing on information provided from the Microsoft forum post, I was brought to these pages:

http://msdn.microsoft.com/en-us/library/bb221142.aspx

And I found this mentioned on the page.



> To make sure the Recordset object supports bookmarks, check the value of its Bookmarkable property before you use the Bookmark property. If the Bookmarkable property is False, the Recordset object doesn't support bookmarks, and using the Bookmark property results in a trappable error.


However, it seems that the bookmark property (_expression_.*Bookmarkable*) is default set to true... But how do I actually define/confirm the bookmarkable setting of a record?

But I'm still convinced that this is not the case, because if it works as an MDB file in Access 2003, why doesn't it work as an MDB (or ACCDB) in Access 2007?


----------



## maxx_eclipse (May 29, 2007)

Well, I got a repsonse on the Microsoft forum:



> I'm surprised that the code has ever worked with any degree of reliability.
> Bookmarks are unique to the recordset of each form and subform.
> 
> If I have one record in the recordset for the main form and 30 records for the
> ...


So yeah, he seems rather surprised that it worked and claims that it really shouldn't (or that it doesn't work as well as it should, I don't know), and then he puts some coding...

And a new reply from an MVP suggests that A2007 is less flexible with incorrectly written coding...



> A2007 is a bit more inflexible when code is not written as expected. Some versions of access have an uncanny ability to guess correctly when code is less than perfect. Most likely, that code that works in A2003 will one day refuse to work in that same application, when access guesses wrongly about your bookmark code.
> 
> A2007 is more picky about code that is less than perfect. It is always a good idea to use the accepted way of writing vba code for an
> access database. There are lots of examples on these discussion groups of how to do that.


----------



## OBP (Mar 8, 2005)

Both forms are based on the same Recordset (Or should be) so that does not apply here.
I like the "It is always a good idea to use the accepted way of writing vba code for an
access database", I would love to know what the Accepted Way is.


----------



## maxx_eclipse (May 29, 2007)

Why don't you guys have a code-off or something?

But still, even after I explained my situation, the only 'acceptable' answer to me is: "A2007 is a bit more inflexible when code is not written as expected."


----------



## maxx_eclipse (May 29, 2007)

Okay, my deadline is in 11 days (Friday the 19th), and I still have to do other updates on the database prior to the release.

So, if the original method of:

MainForm
> SubForm1
>> SubForm2

doesn't work, how would I attempt:

MainForm
> SubForm1
> SubForm2

???


----------



## OBP (Mar 8, 2005)

I don't know if you can as they are now unlinked Bookmarks.
It would be something like this for Subform2
me.bookmark = me.parent.subform1.bookmark
and for subform1
me.bookmark = me.parent.subform2.bookmark


----------



## maxx_eclipse (May 29, 2007)

OBP, I FIXED THE PROBLEM!!!

Okay, so this is what I did:

I changed the format to:

MainForm
> SubForm1
> SubForm2

and then I did the following for "On Enter" on SubForm2 form the MainForm:


```
Private Sub SUBFORM_AGENDA_DATASHEET_Enter()

Set Me.SUBFORM_AGENDA.Form.Recordset = Me.SUBFORM_AGENDA_DATASHEET.Form.Recordset

End Sub
```
And would you believe it, it works! Thank the Lord it works!

I'm so excited, I just can't hide; I'm about to lose control and I think I like it! 

But I wouldn't have gotten this far had it not been for your input OBP, thank you for your time and patience with this matter. I am in your debt.

This topic is now solved.


----------



## OBP (Mar 8, 2005)

Well done in finding that wierd workaround.


----------



## maxx_eclipse (May 29, 2007)

Thanks,

Although I'm still rather perplexed why the bookmark worked in Access 2003 but not Access 2007... I suppose it's a mystery that will never be solved.


----------

