Help needed with simple field problem (0/1)

Hi,

I'm new to Access. I am a programmer, but my only database experience
was when I made a medium sized FoxBase application for a small
business years ago.

The project I've started working on is way to simple to justify
writing an application. The database just needs to have client contact
information, appointment dates, pending messages, and payments
records.

I'm using Microsoft Access 2000.

I started learning this today. Here's my hang-up:

I'm trying to get a field to display a Clients name from another
table. When I select the client ID, while adding a new record (with a
Combo Box), it will only place the ClientID in the record, and not the
name.

I tried to do it the way it is in the sample NWIND database (attached
here) that comes with Access. Select the Orders table. When you add a
new record, there's a drop down for customer name. When you select
one, the name is added to the record (as expected...).

Now open the ClientDB database (attached here), select the Messages
table, and add a new record. When you select a client name (there's
only two), it places the ClientID in the Clients name field. I tried
changing different things, but I can't get it to work. I don't see a
significant difference between what I have and what's in the NWIND
database. I've spent a couple hours trying to get it to display the
clients name.

Any help appreciated.

I have attached the two databases to this message. (The NWIND sample
database open with 5 errors, but it does what I'm doing here okay.)

I have another question. Is there going to be a way to add messages
(table) and appointments (table) from the customers table? (Rather
than selecting the messages table, and then selecting the customers
name from a drop down list.) The person this is for is going to want
to be able to do everything from the customer list. (Adding records,
that is - I will set up reports he can select. He's going to want to
select a customer, and then do whatever with it - add dates, add
messgaes.) If there's not going to be a way to do this (other than
switching to the given table...), I need to stop here and find another
application, rather than learn this one. (If so, suggestions?)

Re: Help needed with simple field problem (0/1)

The Attachments didn't get included.
I just put them on OrbitFiles instead.
You can download them here:
http://www.orbitfiles.com/download/id2209446471.html (ClientDB)
http://www.orbitfiles.com/download/id2209445579.html (ClientDB)
http://www.orbitfiles.com/download/id2209448731.html (NWIND)

On Thu, 15 Nov 2007 05:44:58 -0500, Doug@NoEmail.com wrote:

>Hi,
>
>I'm new to Access. I am a programmer, but my only database experience
>was when I made a medium sized FoxBase application for a small
>business years ago.
>
>The project I've started working on is way to simple to justify
>writing an application. The database just needs to have client contact
>information, appointment dates, pending messages, and payments
>records.
>
>I'm using Microsoft Access 2000.
>
>I started learning this today. Here's my hang-up:
>
>I'm trying to get a field to display a Clients name from another
>table. When I select the client ID, while adding a new record (with a
>Combo Box), it will only place the ClientID in the record, and not the
>name.
>
>I tried to do it the way it is in the sample NWIND database (attached
>here) that comes with Access. Select the Orders table. When you add a
>new record, there's a drop down for customer name. When you select
>one, the name is added to the record (as expected...).
>
>Now open the ClientDB database (attached here), select the Messages
>table, and add a new record. When you select a client name (there's
>only two), it places the ClientID in the Clients name field. I tried
>changing different things, but I can't get it to work. I don't see a
>significant difference between what I have and what's in the NWIND
>database. I've spent a couple hours trying to get it to display the
>clients name.
>
>Any help appreciated.
>
>I have attached the two databases to this message. (The NWIND sample
>database open with 5 errors, but it does what I'm doing here okay.)
>
>I have another question. Is there going to be a way to add messages
>(table) and appointments (table) from the customers table? (Rather
>than selecting the messages table, and then selecting the customers
>name from a drop down list.) The person this is for is going to want
>to be able to do everything from the customer list. (Adding records,
>that is - I will set up reports he can select. He's going to want to
>select a customer, and then do whatever with it - add dates, add
>messgaes.) If there's not going to be a way to do this (other than
>switching to the given table...), I need to stop here and find another
>application, rather than learn this one. (If so, suggestions?)

Re: Help needed with simple field problem (0/1)

> I'm trying to get a field to display a Clients name from another
> table. When I select the client ID, while adding a new record (with a
> Combo Box), it will only place the ClientID in the record, and not the
> name.

Not sure what you're trying to do. But I'll cover all three cases I can
think of

1) If you're trying to simply display the client's name in the combo box,
which is bound to the client ID, then make the combo box have two columns,
with the second column being the name, and set the width of the first column
to 0, which will hide the client ID.

2) If you're trying to populate a client name field in the form that also
has the client ID field (which would be a non-normalized design), then use
the AfterUpdate event of the combo box to populate that field.

3) If (as I think is the case) you want to display information from the
Clients table when you select a Client ID in the form bound to the Messages
table, then what you do is:

a) In the form's recordsource, add the Clients table. So you would have
two tables in the recordsource: Clients and Messages, and join them on the
ClientID field.

b) Bind the client-related controls in your form to the fields from the
Clients table.

c) Make sure your combo box is bound to the ClientID field in the
Messages table.

Now when you select a ClientID from the drop-down, the data from the
fields from the corresponding record in the Clients table should
automatically appear on the form.

> I have another question. Is there going to be a way to add messages
> (table) and appointments (table) from the customers table? (Rather
> than selecting the messages table, and then selecting the customers
> name from a drop down list.) The person this is for is going to want
> to be able to do everything from the customer list

Yes, you can do that one of two ways. Either create subforms in the
Customers form for the Messages and Appointments tables (the subforms will
be linked on the ClientID field, and Access will manage creating and
filtering the records for you); or use pop-ups from the Customers form to
enter Messages and Appointments (here, you'd have to manage the records
yourself).

Obviously, subforms are a lot more convenient, both for you to set up, as
well as for the user to use, since they would be right there on the form,
and the data is visible at all times. The downside of subforms is form real
estate, since they take up room. If you don't have a lot of room to work
with; or if you want a lot of information in your subforms, then pop-ups
might be better. Subforms also use up more system resources, since they're
open all the time. But I don't think that's anything you'd have to worry
about, really.

.. > that is - I will set up reports he can select. He's going to want to
> select a customer, and then do whatever with it - add dates, add
> messgaes.) If there's not going to be a way to do this (other than
> switching to the given table...), I need to stop here and find another
> application, rather than learn this one. (If so, suggestions?)
>

I think you'll find Access subforms to be just what you're looking for. Very
easy to work with, and a very convenient tool.

Neil

Re: Help needed with simple field problem (0/1)

Well, the attachements did show up. I'm using a new newsreader. The
attachement are in a seperate message with a 1/1 appended to the
subject header.

On Thu, 15 Nov 2007 06:04:42 -0500, Doug@NoEmail.com wrote:

>The Attachments didn't get included.
>I just put them on OrbitFiles instead.
>You can download them here:
>http://www.orbitfiles.com/download/id2209446471.html (ClientDB)
>http://www.orbitfiles.com/download/id2209445579.html (ClientDB)
>http://www.orbitfiles.com/download/id2209448731.html (NWIND)
>
>On Thu, 15 Nov 2007 05:44:58 -0500, Doug@NoEmail.com wrote:
>
>>Hi,
>>
>>I'm new to Access. I am a programmer, but my only database experience
>>was when I made a medium sized FoxBase application for a small
>>business years ago.
>>
>>The project I've started working on is way to simple to justify
>>writing an application. The database just needs to have client contact
>>information, appointment dates, pending messages, and payments
>>records.
>>
>>I'm using Microsoft Access 2000.
>>
>>I started learning this today. Here's my hang-up:
>>
>>I'm trying to get a field to display a Clients name from another
>>table. When I select the client ID, while adding a new record (with a
>>Combo Box), it will only place the ClientID in the record, and not the
>>name.
>>
>>I tried to do it the way it is in the sample NWIND database (attached
>>here) that comes with Access. Select the Orders table. When you add a
>>new record, there's a drop down for customer name. When you select
>>one, the name is added to the record (as expected...).
>>
>>Now open the ClientDB database (attached here), select the Messages
>>table, and add a new record. When you select a client name (there's
>>only two), it places the ClientID in the Clients name field. I tried
>>changing different things, but I can't get it to work. I don't see a
>>significant difference between what I have and what's in the NWIND
>>database. I've spent a couple hours trying to get it to display the
>>clients name.
>>
>>Any help appreciated.
>>
>>I have attached the two databases to this message. (The NWIND sample
>>database open with 5 errors, but it does what I'm doing here okay.)
>>
>>I have another question. Is there going to be a way to add messages
>>(table) and appointments (table) from the customers table? (Rather
>>than selecting the messages table, and then selecting the customers
>>name from a drop down list.) The person this is for is going to want
>>to be able to do everything from the customer list. (Adding records,
>>that is - I will set up reports he can select. He's going to want to
>>select a customer, and then do whatever with it - add dates, add
>>messgaes.) If there's not going to be a way to do this (other than
>>switching to the given table...), I need to stop here and find another
>>application, rather than learn this one. (If so, suggestions?)

Re: Help needed with simple field problem (0/1)

It would be redundant to store both the ClientID and ClientName in the
second table. What happens if you change the name in one of the two tables:
how will you know which name is correct?

Only the foreign key (the ClientID) should be stored in the second table.
You can join the two tables together in a query and use the query wherever
you would otherwise have used the table when you need the name.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


wrote in message
news:6s5oj3509ih3mhd7krliig838mb4or2rgh@4ax.com...
> Hi,
>
> I'm new to Access. I am a programmer, but my only database experience
> was when I made a medium sized FoxBase application for a small
> business years ago.
>
> The project I've started working on is way to simple to justify
> writing an application. The database just needs to have client contact
> information, appointment dates, pending messages, and payments
> records.
>
> I'm using Microsoft Access 2000.
>
> I started learning this today. Here's my hang-up:
>
> I'm trying to get a field to display a Clients name from another
> table. When I select the client ID, while adding a new record (with a
> Combo Box), it will only place the ClientID in the record, and not the
> name.
>
> I tried to do it the way it is in the sample NWIND database (attached
> here) that comes with Access. Select the Orders table. When you add a
> new record, there's a drop down for customer name. When you select
> one, the name is added to the record (as expected...).
>
> Now open the ClientDB database (attached here), select the Messages
> table, and add a new record. When you select a client name (there's
> only two), it places the ClientID in the Clients name field. I tried
> changing different things, but I can't get it to work. I don't see a
> significant difference between what I have and what's in the NWIND
> database. I've spent a couple hours trying to get it to display the
> clients name.
>
> Any help appreciated.
>
> I have attached the two databases to this message. (The NWIND sample
> database open with 5 errors, but it does what I'm doing here okay.)
>
> I have another question. Is there going to be a way to add messages
> (table) and appointments (table) from the customers table? (Rather
> than selecting the messages table, and then selecting the customers
> name from a drop down list.) The person this is for is going to want
> to be able to do everything from the customer list. (Adding records,
> that is - I will set up reports he can select. He's going to want to
> select a customer, and then do whatever with it - add dates, add
> messgaes.) If there's not going to be a way to do this (other than
> switching to the given table...), I need to stop here and find another
> application, rather than learn this one. (If so, suggestions?)
>

Re: Help needed with simple field problem (0/1)

The clients name isn't be stored in the Messages table. I just want it
displayed when you are adding messages to the table, like what happens
in the sample NWIND database when you add an order. The problem is I
can't get it to work like NWIND, and I don't see why. The databases
are posted here in a message with the same subject, ending in 1/1
instead of 0/1.

On Thu, 15 Nov 2007 06:56:43 -0500, "Douglas J. Steele"
wrote:

>It would be redundant to store both the ClientID and ClientName in the
>second table. What happens if you change the name in one of the two tables:
>how will you know which name is correct?
>
>Only the foreign key (the ClientID) should be stored in the second table.
>You can join the two tables together in a query and use the query wherever
>you would otherwise have used the table when you need the name.

Re: Help needed with simple field problem (0/1)

On Thu, 15 Nov 2007 11:04:46 GMT, "Neil" wrote:

>
>> I'm trying to get a field to display a Clients name from another
>> table. When I select the client ID, while adding a new record (with a
>> Combo Box), it will only place the ClientID in the record, and not the
>> name.
>
>Not sure what you're trying to do. But I'll cover all three cases I can
>think of
>
>1) If you're trying to simply display the client's name in the combo box,
>which is bound to the client ID, then make the combo box have two columns,
>with the second column being the name, and set the width of the first column
>to 0, which will hide the client ID.
>

I guessed and put 0,1 here, which got converted to 0";1" - which
solved my problem. Thanks. (I hadn't made any forms yet.)

>2) If you're trying to populate a client name field in the form that also
>has the client ID field (which would be a non-normalized design), then use
>the AfterUpdate event of the combo box to populate that field.
>
>3) If (as I think is the case) you want to display information from the
>Clients table when you select a Client ID in the form bound to the Messages
>table, then what you do is:
>
> a) In the form's recordsource, add the Clients table. So you would have
>two tables in the recordsource: Clients and Messages, and join them on the
>ClientID field.
>
> b) Bind the client-related controls in your form to the fields from the
>Clients table.
>
> c) Make sure your combo box is bound to the ClientID field in the
>Messages table.
>
> Now when you select a ClientID from the drop-down, the data from the
>fields from the corresponding record in the Clients table should
>automatically appear on the form.
>
>> I have another question. Is there going to be a way to add messages
>> (table) and appointments (table) from the customers table? (Rather
>> than selecting the messages table, and then selecting the customers
>> name from a drop down list.) The person this is for is going to want
>> to be able to do everything from the customer list
>
>Yes, you can do that one of two ways. Either create subforms in the
>Customers form for the Messages and Appointments tables (the subforms will
>be linked on the ClientID field, and Access will manage creating and
>filtering the records for you); or use pop-ups from the Customers form to
>enter Messages and Appointments (here, you'd have to manage the records
>yourself).
>
>Obviously, subforms are a lot more convenient, both for you to set up, as
>well as for the user to use, since they would be right there on the form,
>and the data is visible at all times. The downside of subforms is form real
>estate, since they take up room. If you don't have a lot of room to work
>with; or if you want a lot of information in your subforms, then pop-ups
>might be better. Subforms also use up more system resources, since they're
>open all the time. But I don't think that's anything you'd have to worry
>about, really.
>
>. > that is - I will set up reports he can select. He's going to want to
>> select a customer, and then do whatever with it - add dates, add
>> messgaes.) If there's not going to be a way to do this (other than
>> switching to the given table...), I need to stop here and find another
>> application, rather than learn this one. (If so, suggestions?)
>>
>
>I think you'll find Access subforms to be just what you're looking for. Very
>easy to work with, and a very convenient tool.
>
>Neil
>

I set up a form, with a subform. This will works well. Thanks.

On the form, I would like a combo box on top, where I can choose a
different client from a list, which changes what record I'm looking at
- like the arrow buttons at the bottom do (which I'd like to remove).
Can this be done with an 'on change' event?

Re: Help needed with simple field problem (0/1)

No, you'd use the AfterUpdate event of the combo box, and then use FindFirst
against the recordsetclone object to find the appropriate record. After
finding the appropriate record, set the form's Bookmark property to the
Recordsetclone's Bookmark property.

But, if you're going to get rid of the navigation buttons at the bottom and
are always going to just display one record, then a better approach would be
to just reset the form's recordsource after a client is selected:

Private Sub MyCombo_AfterUpdate

If Not Isnull(Me.MyCombo) Then
Me.Recordsource = "Select * From MyClientTable Where ClientID=" &
Me.MyCombo
End If

End Sub

You could also refer to a query instead of the table, if you prefer. The
subforms should requery themselves automatically.


> I set up a form, with a subform. This will works well. Thanks.
>
> On the form, I would like a combo box on top, where I can choose a
> different client from a list, which changes what record I'm looking at
> - like the arrow buttons at the bottom do (which I'd like to remove).
> Can this be done with an 'on change' event?
>
>

Re: Help needed with simple field problem (0/1)

On Fri, 16 Nov 2007 07:23:08 GMT, "Neil" wrote:

>No, you'd use the AfterUpdate event of the combo box, and then use FindFirst
>against the recordsetclone object to find the appropriate record. After
>finding the appropriate record, set the form's Bookmark property to the
>Recordsetclone's Bookmark property.
>
>But, if you're going to get rid of the navigation buttons at the bottom and
>are always going to just display one record, then a better approach would be
>to just reset the form's recordsource after a client is selected:
>
>Private Sub MyCombo_AfterUpdate
>
> If Not Isnull(Me.MyCombo) Then
> Me.Recordsource = "Select * From MyClientTable Where ClientID=" &
>Me.MyCombo
> End If
>
>End Sub

That worked. Thanks again.

When new records are added, they don't show up in the combo box until
I close the client form and reopen it.

>
>You could also refer to a query instead of the table, if you prefer. The
>subforms should requery themselves automatically.
>
>
>> I set up a form, with a subform. This will works well. Thanks.
>>
>> On the form, I would like a combo box on top, where I can choose a
>> different client from a list, which changes what record I'm looking at
>> - like the arrow buttons at the bottom do (which I'd like to remove).
>> Can this be done with an 'on change' event?
>>
>>
>

Re: Help needed with simple field problem (0/1)

On Fri, 16 Nov 2007 23:14:44 -0500, Doug@NoEmail.com wrote:

>When new records are added, they don't show up in the combo box until
>I close the client form and reopen it.

Requery the combo box in the code which adds the new records (the form's
AfterUpdate event for example).

John W. Vinson [MVP]

Re: Help needed with simple field problem (0/1)

How are you adding new records? In the above, you stated that you wanted to
take out the record selectors and only show one record at a time. If so,
then you should not let your users add new records by going to the new
record in the form (because then you'll have two records in the form instead
of one, when they add a new record). So, how are you adding new records?

>
> That worked. Thanks again.
>
> When new records are added, they don't show up in the combo box until
> I close the client form and reopen it.
>

Re: Help needed with simple field problem (0/1)

I added records using the record selectors. I haven't figured out how
to delete them yet - they don't appear on the Design View screen. I
haven't looked that hard - been doing other things.

Once I use the combo box to select a record, the record selector count
changes to 1. I can still arrow over to 2 and enter a new record.
When I do that the new record doesn't show up in the combo box list
(the others still do). If I close the form and reopen it, the combo
box sees the new record(s).

On Sat, 17 Nov 2007 00:49:12 -0600, "Neil" wrote:
>How are you adding new records? In the above, you stated that you wanted to
>take out the record selectors and only show one record at a time. If so,
>then you should not let your users add new records by going to the new
>record in the form (because then you'll have two records in the form instead
>of one, when they add a new record). So, how are you adding new records?
>
>>
>> That worked. Thanks again.
>>
>> When new records are added, they don't show up in the combo box until
>> I close the client form and reopen it.
>>
>

Re: Help needed with simple field problem (0/1)

wrote in message
news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>I added records using the record selectors. I haven't figured out how
> to delete them yet - they don't appear on the Design View screen. I
> haven't looked that hard - been doing other things.

By "record selectors" I assume you mean "navigation buttons" at the bottom
of the screen, right? (The record selector is the vertical bar on the left
that has an arrowhead in it.)

You can turn off the navigation bar and/or the record selector in the form's
property. They're both listed there with Yes/No options.

>
> Once I use the combo box to select a record, the record selector count
> changes to 1. I can still arrow over to 2 and enter a new record.
> When I do that the new record doesn't show up in the combo box list
> (the others still do). If I close the form and reopen it, the combo
> box sees the new record(s).

Well, as John noted, just requery the combo box when a new record is
inserted (I'd do the After Insert event of the form). Just do:

Me.MyComboBox.Requery

That's all.

But, as I noted, you shouldn't add records with the navigation buttons if
you're only displaying one record at a time. So, here's what you do:

1) In the form's properties, set Allow Additions to No. That will prevent
the user from adding a record manually.

2) Add a button to your form for adding records. When the user clicks the
button, prompt them for any required field information. If there is no
required field information, and if the primary key is automatically
generated, then you don't need a prompt.

3) In the button's On Click event, after the form (if any) prompts for
values, do the following:

=======================================
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

rs.AddNew
rs!FieldPK = somevalue
rs!OtherField = someothervalue 'This is optional
rs.Update

Me.Recordsource = "Select * From MyTable Where FieldPK="& somevalue

rs.close
set rs = nothing
=========================================

The above is for if you prompt the user for the PK (customer ID?). If the PK
is an autonumber PK, then you'd do this instead:

=========================================
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)

rs.AddNew
rs!OtherField = someothervalue 'This is optional.
rs.Update

rs.bookmark = rs.LastModified
Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK

rs.close
set rs = nothing
=========================================

4) While in a code module, go to Tools | References. Uncheck Microsoft
ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
then scroll down the list, and check the highest numbered version of it.

And that should do it.

Neil



>
> On Sat, 17 Nov 2007 00:49:12 -0600, "Neil" wrote:
>>How are you adding new records? In the above, you stated that you wanted
>>to
>>take out the record selectors and only show one record at a time. If so,
>>then you should not let your users add new records by going to the new
>>record in the form (because then you'll have two records in the form
>>instead
>>of one, when they add a new record). So, how are you adding new records?
>>
>>>
>>> That worked. Thanks again.
>>>
>>> When new records are added, they don't show up in the combo box until
>>> I close the client form and reopen it.
>>>
>>

Re: Help needed with simple field problem (0/1)

On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:

>
> wrote in message
>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
(SNIP)
>>
>> Once I use the combo box to select a record, the record selector count
>> changes to 1. I can still arrow over to 2 and enter a new record.
>> When I do that the new record doesn't show up in the combo box list
>> (the others still do). If I close the form and reopen it, the combo
>> box sees the new record(s).
>
>Well, as John noted, just requery the combo box when a new record is
>inserted (I'd do the After Insert event of the form). Just do:
>
> Me.MyComboBox.Requery
>

I've put that in three places (below). The only thing that updates the
combo box is closing the form and reopening it.

Private Sub Combo36_GotFocus()
Me.Combo36.Requery
End Sub

Private Sub Form_AfterInsert()
Me.Combo36.Requery
End Sub

Private Sub Form_AfterUpdate()
Me.Combo36.Requery
End Sub


(SNIP)

>The above is for if you prompt the user for the PK (customer ID?). If the PK
>is an autonumber PK, then you'd do this instead:
>
>=========================================
>Dim rs As Recordset
>
>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>
>rs.AddNew
>rs!OtherField = someothervalue 'This is optional.
>rs.Update
>
>rs.bookmark = rs.LastModified
>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>
>rs.close
>set rs = nothing
>=========================================

That worked. Thanks.

>
>4) While in a code module, go to Tools | References. Uncheck Microsoft
>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
>then scroll down the list, and check the highest numbered version of it.
>
>And that should do it.
>
>Neil
>

(SNIP)

Re: Help needed with simple field problem (0/1)

On Sat, 17 Nov 2007 18:50:46 -0500, Doug@NoEmail.com wrote:

>On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>
>>
>> wrote in message
>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>(SNIP)
>>>
>>> Once I use the combo box to select a record, the record selector count
>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>> When I do that the new record doesn't show up in the combo box list
>>> (the others still do). If I close the form and reopen it, the combo
>>> box sees the new record(s).
>>
>>Well, as John noted, just requery the combo box when a new record is
>>inserted (I'd do the After Insert event of the form). Just do:
>>
>> Me.MyComboBox.Requery
>>
>
>I've put that in three places (below). The only thing that updates the
>combo box is closing the form and reopening it.
>
>Private Sub Combo36_GotFocus()
> Me.Combo36.Requery
>End Sub
>
>Private Sub Form_AfterInsert()
> Me.Combo36.Requery
>End Sub
>

I usually use the OnEnter event when I want to requery a combo.
Wayne Gillespie
Gosford NSW Australia

Re: Help needed with simple field problem (0/1)

Well, it should work. What's the rowsource of your combo box? Please copy
and paste it here exactly.


wrote in message
news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>
>>
>> wrote in message
>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
> (SNIP)
>>>
>>> Once I use the combo box to select a record, the record selector count
>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>> When I do that the new record doesn't show up in the combo box list
>>> (the others still do). If I close the form and reopen it, the combo
>>> box sees the new record(s).
>>
>>Well, as John noted, just requery the combo box when a new record is
>>inserted (I'd do the After Insert event of the form). Just do:
>>
>> Me.MyComboBox.Requery
>>
>
> I've put that in three places (below). The only thing that updates the
> combo box is closing the form and reopening it.
>
> Private Sub Combo36_GotFocus()
> Me.Combo36.Requery
> End Sub
>
> Private Sub Form_AfterInsert()
> Me.Combo36.Requery
> End Sub
>
> Private Sub Form_AfterUpdate()
> Me.Combo36.Requery
> End Sub
>
>
> (SNIP)
>
>>The above is for if you prompt the user for the PK (customer ID?). If the
>>PK
>>is an autonumber PK, then you'd do this instead:
>>
>>=========================================
>>Dim rs As Recordset
>>
>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>
>>rs.AddNew
>>rs!OtherField = someothervalue 'This is optional.
>>rs.Update
>>
>>rs.bookmark = rs.LastModified
>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>
>>rs.close
>>set rs = nothing
>>=========================================
>
> That worked. Thanks.
>
>>
>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
>>then scroll down the list, and check the highest numbered version of it.
>>
>>And that should do it.
>>
>>Neil
>>
>
> (SNIP)

Re: Help needed with simple field problem (0/1)

Problem with the OnEnter event is that then you're requerying the combo
every time you enter it, instead of just when the data changes.


> I usually use the OnEnter event when I want to requery a combo.
> Wayne Gillespie
> Gosford NSW Australia

Re: Help needed with simple field problem (0/1)

Also, please note how you're adding records. Are you still adding them
manually through the form, or did you switch to adding them in code? The
requery of the combo box should work either way; but I just need to know
which method we're dealing with.


wrote in message
news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>
>>
>> wrote in message
>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
> (SNIP)
>>>
>>> Once I use the combo box to select a record, the record selector count
>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>> When I do that the new record doesn't show up in the combo box list
>>> (the others still do). If I close the form and reopen it, the combo
>>> box sees the new record(s).
>>
>>Well, as John noted, just requery the combo box when a new record is
>>inserted (I'd do the After Insert event of the form). Just do:
>>
>> Me.MyComboBox.Requery
>>
>
> I've put that in three places (below). The only thing that updates the
> combo box is closing the form and reopening it.
>
> Private Sub Combo36_GotFocus()
> Me.Combo36.Requery
> End Sub
>
> Private Sub Form_AfterInsert()
> Me.Combo36.Requery
> End Sub
>
> Private Sub Form_AfterUpdate()
> Me.Combo36.Requery
> End Sub
>
>
> (SNIP)
>
>>The above is for if you prompt the user for the PK (customer ID?). If the
>>PK
>>is an autonumber PK, then you'd do this instead:
>>
>>=========================================
>>Dim rs As Recordset
>>
>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>
>>rs.AddNew
>>rs!OtherField = someothervalue 'This is optional.
>>rs.Update
>>
>>rs.bookmark = rs.LastModified
>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>
>>rs.close
>>set rs = nothing
>>=========================================
>
> That worked. Thanks.
>
>>
>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
>>then scroll down the list, and check the highest numbered version of it.
>>
>>And that should do it.
>>
>>Neil
>>
>
> (SNIP)

Re: Help needed with simple field problem (0/1)

On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:

>Well, it should work. What's the rowsource of your combo box? Please copy
>and paste it here exactly.

Row Source Type: Table/Query
Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
[Clients].[MiddleName], [Clients].[LastName] FROM Clients;

I am adding records from a control button on the form now.

I tested this again, and now the combo box is updated with
Combo36_gotFocus(). I'd prefer to only update that when necessary,
not every time a new record is selected... I don't know why that
works now. The combo box is still not updated with either
Form_AfterInsert() or Form_AfterUpdate().

>
> wrote in message
>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>>
>>>
>>> wrote in message
>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>> (SNIP)
>>>>
>>>> Once I use the combo box to select a record, the record selector count
>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>> When I do that the new record doesn't show up in the combo box list
>>>> (the others still do). If I close the form and reopen it, the combo
>>>> box sees the new record(s).
>>>
>>>Well, as John noted, just requery the combo box when a new record is
>>>inserted (I'd do the After Insert event of the form). Just do:
>>>
>>> Me.MyComboBox.Requery
>>>
>>
>> I've put that in three places (below). The only thing that updates the
>> combo box is closing the form and reopening it.
>>
>> Private Sub Combo36_GotFocus()
>> Me.Combo36.Requery
>> End Sub
>>
>> Private Sub Form_AfterInsert()
>> Me.Combo36.Requery
>> End Sub
>>
>> Private Sub Form_AfterUpdate()
>> Me.Combo36.Requery
>> End Sub
>>
>>
>> (SNIP)
>>
>>>The above is for if you prompt the user for the PK (customer ID?). If the
>>>PK
>>>is an autonumber PK, then you'd do this instead:
>>>
>>>=========================================
>>>Dim rs As Recordset
>>>
>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>
>>>rs.AddNew
>>>rs!OtherField = someothervalue 'This is optional.
>>>rs.Update
>>>
>>>rs.bookmark = rs.LastModified
>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>
>>>rs.close
>>>set rs = nothing
>>>=========================================
>>
>> That worked. Thanks.
>>
>>>
>>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked,
>>>then scroll down the list, and check the highest numbered version of it.
>>>
>>>And that should do it.
>>>
>>>Neil
>>>
>>
>> (SNIP)
>

Re: Help needed with simple field problem (0/1)

Well, if you changed the code to add records from a command button, per the
instructions I sent, then that would explain why it's not working from the
form's AfterUpdate or AfterInsert event: the form is not inserting the
record; you are, though the code. So the AfterUpdate and AfterInsert events
wouldn't apply.

Just put the combo box requery code at the end of the routine that adds the
new record, and you should be fine.


wrote in message
news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>
>>Well, it should work. What's the rowsource of your combo box? Please copy
>>and paste it here exactly.
>
> Row Source Type: Table/Query
> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>
> I am adding records from a control button on the form now.
>
> I tested this again, and now the combo box is updated with
> Combo36_gotFocus(). I'd prefer to only update that when necessary,
> not every time a new record is selected... I don't know why that
> works now. The combo box is still not updated with either
> Form_AfterInsert() or Form_AfterUpdate().
>
>>
>> wrote in message
>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>>>
>>>>
>>>> wrote in message
>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>> (SNIP)
>>>>>
>>>>> Once I use the combo box to select a record, the record selector count
>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>> When I do that the new record doesn't show up in the combo box list
>>>>> (the others still do). If I close the form and reopen it, the combo
>>>>> box sees the new record(s).
>>>>
>>>>Well, as John noted, just requery the combo box when a new record is
>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>
>>>> Me.MyComboBox.Requery
>>>>
>>>
>>> I've put that in three places (below). The only thing that updates the
>>> combo box is closing the form and reopening it.
>>>
>>> Private Sub Combo36_GotFocus()
>>> Me.Combo36.Requery
>>> End Sub
>>>
>>> Private Sub Form_AfterInsert()
>>> Me.Combo36.Requery
>>> End Sub
>>>
>>> Private Sub Form_AfterUpdate()
>>> Me.Combo36.Requery
>>> End Sub
>>>
>>>
>>> (SNIP)
>>>
>>>>The above is for if you prompt the user for the PK (customer ID?). If
>>>>the
>>>>PK
>>>>is an autonumber PK, then you'd do this instead:
>>>>
>>>>=========================================
>>>>Dim rs As Recordset
>>>>
>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>
>>>>rs.AddNew
>>>>rs!OtherField = someothervalue 'This is optional.
>>>>rs.Update
>>>>
>>>>rs.bookmark = rs.LastModified
>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>
>>>>rs.close
>>>>set rs = nothing
>>>>=========================================
>>>
>>> That worked. Thanks.
>>>
>>>>
>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>checked,
>>>>then scroll down the list, and check the highest numbered version of it.
>>>>
>>>>And that should do it.
>>>>
>>>>Neil
>>>>
>>>
>>> (SNIP)
>>

Re: Help needed with simple field problem (0/1)

On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:

>Well, if you changed the code to add records from a command button, per the
>instructions I sent, then that would explain why it's not working from the
>form's AfterUpdate or AfterInsert event: the form is not inserting the
>record; you are, though the code. So the AfterUpdate and AfterInsert events
>wouldn't apply.
>
>Just put the combo box requery code at the end of the routine that adds the
>new record, and you should be fine.
>

I tried that, but I don't know how to reference it, and haven't gotten
around to searching Google for that. "Me" is not pointing to the form
in the add record routine. (There is no "Combo36" in the list after
you type "Me." - like there is in the AfterUpdate routine.) I need to
get a basic book on using Access (just systax, I know how to program -
I've also never used VB before). It took me about 30 minutes
searching Google to find how to change a fields value... (you use the
"clone"...) Do you know of a good/short online reference?


>
> wrote in message
>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>
>>>Well, it should work. What's the rowsource of your combo box? Please copy
>>>and paste it here exactly.
>>
>> Row Source Type: Table/Query
>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>
>> I am adding records from a control button on the form now.
>>
>> I tested this again, and now the combo box is updated with
>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>> not every time a new record is selected... I don't know why that
>> works now. The combo box is still not updated with either
>> Form_AfterInsert() or Form_AfterUpdate().
>>
>>>
>>> wrote in message
>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>>>>
>>>>>
>>>>> wrote in message
>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>> (SNIP)
>>>>>>
>>>>>> Once I use the combo box to select a record, the record selector count
>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>> When I do that the new record doesn't show up in the combo box list
>>>>>> (the others still do). If I close the form and reopen it, the combo
>>>>>> box sees the new record(s).
>>>>>
>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>
>>>>> Me.MyComboBox.Requery
>>>>>
>>>>
>>>> I've put that in three places (below). The only thing that updates the
>>>> combo box is closing the form and reopening it.
>>>>
>>>> Private Sub Combo36_GotFocus()
>>>> Me.Combo36.Requery
>>>> End Sub
>>>>
>>>> Private Sub Form_AfterInsert()
>>>> Me.Combo36.Requery
>>>> End Sub
>>>>
>>>> Private Sub Form_AfterUpdate()
>>>> Me.Combo36.Requery
>>>> End Sub
>>>>
>>>>
>>>> (SNIP)
>>>>
>>>>>The above is for if you prompt the user for the PK (customer ID?). If
>>>>>the
>>>>>PK
>>>>>is an autonumber PK, then you'd do this instead:
>>>>>
>>>>>=========================================
>>>>>Dim rs As Recordset
>>>>>
>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>
>>>>>rs.AddNew
>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>rs.Update
>>>>>
>>>>>rs.bookmark = rs.LastModified
>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>
>>>>>rs.close
>>>>>set rs = nothing
>>>>>=========================================
>>>>
>>>> That worked. Thanks.
>>>>
>>>>>
>>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>checked,
>>>>>then scroll down the list, and check the highest numbered version of it.
>>>>>
>>>>>And that should do it.
>>>>>
>>>>>Neil
>>>>>
>>>>
>>>> (SNIP)
>>>
>

Re: Help needed with simple field problem (0/1)

OK, we need to back up a second here. You said that the routine I sent to
add a record was working. That routine referenced Me in the Me.Recordsource=
item. But you write: "'Me' is not pointing to the form in the add record
routine." So, if "Me" is not pointing to the form in the add record routine,
then how does the Me.Recordsource= line in the add record routine work? If
Me works in one line of the routine, it will work in the other line of the
same routine.

So, I assume that the add record routine is in your button's OnClick event,
and it resides in the form's code module, correct? If so, then Me will work.
If you're not seeing Combo36, then it's either not in the form's code
module, or there's a compile error and that has to be resolved first. Try
compiling and see what happens.


wrote in message
news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>
>>Well, if you changed the code to add records from a command button, per
>>the
>>instructions I sent, then that would explain why it's not working from the
>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>events
>>wouldn't apply.
>>
>>Just put the combo box requery code at the end of the routine that adds
>>the
>>new record, and you should be fine.
>>
>
> I tried that, but I don't know how to reference it, and haven't gotten
> around to searching Google for that. "Me" is not pointing to the form
> in the add record routine. (There is no "Combo36" in the list after
> you type "Me." - like there is in the AfterUpdate routine.) I need to
> get a basic book on using Access (just systax, I know how to program -
> I've also never used VB before). It took me about 30 minutes
> searching Google to find how to change a fields value... (you use the
> "clone"...) Do you know of a good/short online reference?
>
>
>>
>> wrote in message
>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>
>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>copy
>>>>and paste it here exactly.
>>>
>>> Row Source Type: Table/Query
>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>
>>> I am adding records from a control button on the form now.
>>>
>>> I tested this again, and now the combo box is updated with
>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>> not every time a new record is selected... I don't know why that
>>> works now. The combo box is still not updated with either
>>> Form_AfterInsert() or Form_AfterUpdate().
>>>
>>>>
>>>> wrote in message
>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>> (SNIP)
>>>>>>>
>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>> count
>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>>> When I do that the new record doesn't show up in the combo box list
>>>>>>> (the others still do). If I close the form and reopen it, the combo
>>>>>>> box sees the new record(s).
>>>>>>
>>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>
>>>>>> Me.MyComboBox.Requery
>>>>>>
>>>>>
>>>>> I've put that in three places (below). The only thing that updates the
>>>>> combo box is closing the form and reopening it.
>>>>>
>>>>> Private Sub Combo36_GotFocus()
>>>>> Me.Combo36.Requery
>>>>> End Sub
>>>>>
>>>>> Private Sub Form_AfterInsert()
>>>>> Me.Combo36.Requery
>>>>> End Sub
>>>>>
>>>>> Private Sub Form_AfterUpdate()
>>>>> Me.Combo36.Requery
>>>>> End Sub
>>>>>
>>>>>
>>>>> (SNIP)
>>>>>
>>>>>>The above is for if you prompt the user for the PK (customer ID?). If
>>>>>>the
>>>>>>PK
>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>
>>>>>>=========================================
>>>>>>Dim rs As Recordset
>>>>>>
>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>
>>>>>>rs.AddNew
>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>rs.Update
>>>>>>
>>>>>>rs.bookmark = rs.LastModified
>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>>
>>>>>>rs.close
>>>>>>set rs = nothing
>>>>>>=========================================
>>>>>
>>>>> That worked. Thanks.
>>>>>
>>>>>>
>>>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>checked,
>>>>>>then scroll down the list, and check the highest numbered version of
>>>>>>it.
>>>>>>
>>>>>>And that should do it.
>>>>>>
>>>>>>Neil
>>>>>>
>>>>>
>>>>> (SNIP)
>>>>
>>

Re: Help needed with simple field problem (0/1)

No, I'm not familiar with an online reference, though someone else here
might be. I would just go to my local Barnes and Noble or Borders and pick
up a beginning or intermediate Access programming book.


> you type "Me." - like there is in the AfterUpdate routine.) I need to
> get a basic book on using Access (just systax, I know how to program -
> I've also never used VB before). It took me about 30 minutes
> searching Google to find how to change a fields value... (you use the
> "clone"...) Do you know of a good/short online reference?
>
>

Re: Help needed with simple field problem (0/1)

On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:

>No, I'm not familiar with an online reference, though someone else here
>might be. I would just go to my local Barnes and Noble or Borders and pick
>up a beginning or intermediate Access programming book.

Here are my (incomplete) list of useful online references...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

Re: Help needed with simple field problem (0/1)

Thanks for providing that. I'm sure the OP will find that very useful. I'm
going to save it for future reference, myself.

"John W. Vinson" wrote in message
news:1jn3k3583fe72tbennje2a8pad5715bsrd@4ax.com...
> On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>
>>No, I'm not familiar with an online reference, though someone else here
>>might be. I would just go to my local Barnes and Noble or Borders and pick
>>up a beginning or intermediate Access programming book.
>
> Here are my (incomplete) list of useful online references...
>
> Jeff Conrad's resources page:
> http://www.accessmvp.com/JConrad/accessjunkie/resources.html
>
> The Access Web resources page:
> http://www.mvps.org/access/resources/index.html
>
> A free tutorial written by Crystal (MS Access MVP):
> http://allenbrowne.com/casu-22.html
>
> MVP Allen Browne's tutorials:
> http://allenbrowne.com/links.html#Tutorials
>
> John W. Vinson [MVP]

Re: Help needed with simple field problem (0/1)

Your right. I must have made a typo. There's a new problem. The new
record is not sorted in the combo box, it's at the top of the list.
When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
is resorted. I did a couple tests, and this behavior is consistent.
(I put the requery just before exit sub in the add record routine.)


On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:

>OK, we need to back up a second here. You said that the routine I sent to
>add a record was working. That routine referenced Me in the Me.Recordsource=
>item. But you write: "'Me' is not pointing to the form in the add record
>routine." So, if "Me" is not pointing to the form in the add record routine,
>then how does the Me.Recordsource= line in the add record routine work? If
>Me works in one line of the routine, it will work in the other line of the
>same routine.
>
>So, I assume that the add record routine is in your button's OnClick event,
>and it resides in the form's code module, correct? If so, then Me will work.
>If you're not seeing Combo36, then it's either not in the form's code
>module, or there's a compile error and that has to be resolved first. Try
>compiling and see what happens.
>
>
> wrote in message
>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>
>>>Well, if you changed the code to add records from a command button, per
>>>the
>>>instructions I sent, then that would explain why it's not working from the
>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>events
>>>wouldn't apply.
>>>
>>>Just put the combo box requery code at the end of the routine that adds
>>>the
>>>new record, and you should be fine.
>>>
>>
>> I tried that, but I don't know how to reference it, and haven't gotten
>> around to searching Google for that. "Me" is not pointing to the form
>> in the add record routine. (There is no "Combo36" in the list after
>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>> get a basic book on using Access (just systax, I know how to program -
>> I've also never used VB before). It took me about 30 minutes
>> searching Google to find how to change a fields value... (you use the
>> "clone"...) Do you know of a good/short online reference?
>>
>>
>>>
>>> wrote in message
>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>
>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>copy
>>>>>and paste it here exactly.
>>>>
>>>> Row Source Type: Table/Query
>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>
>>>> I am adding records from a control button on the form now.
>>>>
>>>> I tested this again, and now the combo box is updated with
>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>> not every time a new record is selected... I don't know why that
>>>> works now. The combo box is still not updated with either
>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>
>>>>>
>>>>> wrote in message
>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil" wrote:
>>>>>>
>>>>>>>
>>>>>>> wrote in message
>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>> (SNIP)
>>>>>>>>
>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>> count
>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>>>> When I do that the new record doesn't show up in the combo box list
>>>>>>>> (the others still do). If I close the form and reopen it, the combo
>>>>>>>> box sees the new record(s).
>>>>>>>
>>>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>
>>>>>>> Me.MyComboBox.Requery
>>>>>>>
>>>>>>
>>>>>> I've put that in three places (below). The only thing that updates the
>>>>>> combo box is closing the form and reopening it.
>>>>>>
>>>>>> Private Sub Combo36_GotFocus()
>>>>>> Me.Combo36.Requery
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub Form_AfterInsert()
>>>>>> Me.Combo36.Requery
>>>>>> End Sub
>>>>>>
>>>>>> Private Sub Form_AfterUpdate()
>>>>>> Me.Combo36.Requery
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>> (SNIP)
>>>>>>
>>>>>>>The above is for if you prompt the user for the PK (customer ID?). If
>>>>>>>the
>>>>>>>PK
>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>
>>>>>>>=========================================
>>>>>>>Dim rs As Recordset
>>>>>>>
>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>
>>>>>>>rs.AddNew
>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>rs.Update
>>>>>>>
>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>>>
>>>>>>>rs.close
>>>>>>>set rs = nothing
>>>>>>>=========================================
>>>>>>
>>>>>> That worked. Thanks.
>>>>>>
>>>>>>>
>>>>>>>4) While in a code module, go to Tools | References. Uncheck Microsoft
>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>checked,
>>>>>>>then scroll down the list, and check the highest numbered version of
>>>>>>>it.
>>>>>>>
>>>>>>>And that should do it.
>>>>>>>
>>>>>>>Neil
>>>>>>>
>>>>>>
>>>>>> (SNIP)
>>>>>
>>>
>

Re: Help needed with simple field problem (0/1)

Where you place the requery command should have no effect on the sorting of
the combo box. Is you combo explicitly sorted? If so, then this shouldn't be
happening. If not, then there might be some weird glitch with a new record
being at the top, etc. Either way, just make sure you have an explicit sort
in your combo's rowsource, and you should fine.


wrote in message
news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com...
> Your right. I must have made a typo. There's a new problem. The new
> record is not sorted in the combo box, it's at the top of the list.
> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
> is resorted. I did a couple tests, and this behavior is consistent.
> (I put the requery just before exit sub in the add record routine.)
>
>
> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:
>
>>OK, we need to back up a second here. You said that the routine I sent to
>>add a record was working. That routine referenced Me in the
>>Me.Recordsource=
>>item. But you write: "'Me' is not pointing to the form in the add record
>>routine." So, if "Me" is not pointing to the form in the add record
>>routine,
>>then how does the Me.Recordsource= line in the add record routine work? If
>>Me works in one line of the routine, it will work in the other line of the
>>same routine.
>>
>>So, I assume that the add record routine is in your button's OnClick
>>event,
>>and it resides in the form's code module, correct? If so, then Me will
>>work.
>>If you're not seeing Combo36, then it's either not in the form's code
>>module, or there's a compile error and that has to be resolved first. Try
>>compiling and see what happens.
>>
>>
>> wrote in message
>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>>
>>>>Well, if you changed the code to add records from a command button, per
>>>>the
>>>>instructions I sent, then that would explain why it's not working from
>>>>the
>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>>events
>>>>wouldn't apply.
>>>>
>>>>Just put the combo box requery code at the end of the routine that adds
>>>>the
>>>>new record, and you should be fine.
>>>>
>>>
>>> I tried that, but I don't know how to reference it, and haven't gotten
>>> around to searching Google for that. "Me" is not pointing to the form
>>> in the add record routine. (There is no "Combo36" in the list after
>>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>>> get a basic book on using Access (just systax, I know how to program -
>>> I've also never used VB before). It took me about 30 minutes
>>> searching Google to find how to change a fields value... (you use the
>>> "clone"...) Do you know of a good/short online reference?
>>>
>>>
>>>>
>>>> wrote in message
>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>>
>>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>>copy
>>>>>>and paste it here exactly.
>>>>>
>>>>> Row Source Type: Table/Query
>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>>
>>>>> I am adding records from a control button on the form now.
>>>>>
>>>>> I tested this again, and now the combo box is updated with
>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>>> not every time a new record is selected... I don't know why that
>>>>> works now. The combo box is still not updated with either
>>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil"
>>>>>>> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> wrote in message
>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>>> (SNIP)
>>>>>>>>>
>>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>>> count
>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>>>>> When I do that the new record doesn't show up in the combo box
>>>>>>>>> list
>>>>>>>>> (the others still do). If I close the form and reopen it, the
>>>>>>>>> combo
>>>>>>>>> box sees the new record(s).
>>>>>>>>
>>>>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>>
>>>>>>>> Me.MyComboBox.Requery
>>>>>>>>
>>>>>>>
>>>>>>> I've put that in three places (below). The only thing that updates
>>>>>>> the
>>>>>>> combo box is closing the form and reopening it.
>>>>>>>
>>>>>>> Private Sub Combo36_GotFocus()
>>>>>>> Me.Combo36.Requery
>>>>>>> End Sub
>>>>>>>
>>>>>>> Private Sub Form_AfterInsert()
>>>>>>> Me.Combo36.Requery
>>>>>>> End Sub
>>>>>>>
>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>> Me.Combo36.Requery
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>> (SNIP)
>>>>>>>
>>>>>>>>The above is for if you prompt the user for the PK (customer ID?).
>>>>>>>>If
>>>>>>>>the
>>>>>>>>PK
>>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>>
>>>>>>>>=========================================
>>>>>>>>Dim rs As Recordset
>>>>>>>>
>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>>
>>>>>>>>rs.AddNew
>>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>>rs.Update
>>>>>>>>
>>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>>>>
>>>>>>>>rs.close
>>>>>>>>set rs = nothing
>>>>>>>>=========================================
>>>>>>>
>>>>>>> That worked. Thanks.
>>>>>>>
>>>>>>>>
>>>>>>>>4) While in a code module, go to Tools | References. Uncheck
>>>>>>>>Microsoft
>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>>checked,
>>>>>>>>then scroll down the list, and check the highest numbered version of
>>>>>>>>it.
>>>>>>>>
>>>>>>>>And that should do it.
>>>>>>>>
>>>>>>>>Neil
>>>>>>>>
>>>>>>>
>>>>>>> (SNIP)
>>>>>>
>>>>
>>

Re: Help needed with simple field problem (0/1)

Here's the combo's rowsource:
SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER
BY [Clients].[FullName];

On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" wrote:

>Where you place the requery command should have no effect on the sorting of
>the combo box. Is you combo explicitly sorted? If so, then this shouldn't be
>happening. If not, then there might be some weird glitch with a new record
>being at the top, etc. Either way, just make sure you have an explicit sort
>in your combo's rowsource, and you should fine.
>
>
> wrote in message
>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com...
>> Your right. I must have made a typo. There's a new problem. The new
>> record is not sorted in the combo box, it's at the top of the list.
>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
>> is resorted. I did a couple tests, and this behavior is consistent.
>> (I put the requery just before exit sub in the add record routine.)
>>
>>
>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:
>>
>>>OK, we need to back up a second here. You said that the routine I sent to
>>>add a record was working. That routine referenced Me in the
>>>Me.Recordsource=
>>>item. But you write: "'Me' is not pointing to the form in the add record
>>>routine." So, if "Me" is not pointing to the form in the add record
>>>routine,
>>>then how does the Me.Recordsource= line in the add record routine work? If
>>>Me works in one line of the routine, it will work in the other line of the
>>>same routine.
>>>
>>>So, I assume that the add record routine is in your button's OnClick
>>>event,
>>>and it resides in the form's code module, correct? If so, then Me will
>>>work.
>>>If you're not seeing Combo36, then it's either not in the form's code
>>>module, or there's a compile error and that has to be resolved first. Try
>>>compiling and see what happens.
>>>
>>>
>>> wrote in message
>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>>>
>>>>>Well, if you changed the code to add records from a command button, per
>>>>>the
>>>>>instructions I sent, then that would explain why it's not working from
>>>>>the
>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>>>events
>>>>>wouldn't apply.
>>>>>
>>>>>Just put the combo box requery code at the end of the routine that adds
>>>>>the
>>>>>new record, and you should be fine.
>>>>>
>>>>
>>>> I tried that, but I don't know how to reference it, and haven't gotten
>>>> around to searching Google for that. "Me" is not pointing to the form
>>>> in the add record routine. (There is no "Combo36" in the list after
>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>>>> get a basic book on using Access (just systax, I know how to program -
>>>> I've also never used VB before). It took me about 30 minutes
>>>> searching Google to find how to change a fields value... (you use the
>>>> "clone"...) Do you know of a good/short online reference?
>>>>
>>>>
>>>>>
>>>>> wrote in message
>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>>>
>>>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>>>copy
>>>>>>>and paste it here exactly.
>>>>>>
>>>>>> Row Source Type: Table/Query
>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>>>
>>>>>> I am adding records from a control button on the form now.
>>>>>>
>>>>>> I tested this again, and now the combo box is updated with
>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>>>> not every time a new record is selected... I don't know why that
>>>>>> works now. The combo box is still not updated with either
>>>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>>>
>>>>>>>
>>>>>>> wrote in message
>>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil"
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>> wrote in message
>>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>>>> (SNIP)
>>>>>>>>>>
>>>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>>>> count
>>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>>>>>> When I do that the new record doesn't show up in the combo box
>>>>>>>>>> list
>>>>>>>>>> (the others still do). If I close the form and reopen it, the
>>>>>>>>>> combo
>>>>>>>>>> box sees the new record(s).
>>>>>>>>>
>>>>>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>>>
>>>>>>>>> Me.MyComboBox.Requery
>>>>>>>>>
>>>>>>>>
>>>>>>>> I've put that in three places (below). The only thing that updates
>>>>>>>> the
>>>>>>>> combo box is closing the form and reopening it.
>>>>>>>>
>>>>>>>> Private Sub Combo36_GotFocus()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub Form_AfterInsert()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>> (SNIP)
>>>>>>>>
>>>>>>>>>The above is for if you prompt the user for the PK (customer ID?).
>>>>>>>>>If
>>>>>>>>>the
>>>>>>>>>PK
>>>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>>>
>>>>>>>>>=========================================
>>>>>>>>>Dim rs As Recordset
>>>>>>>>>
>>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>>>
>>>>>>>>>rs.AddNew
>>>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>>>rs.Update
>>>>>>>>>
>>>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>>>>>
>>>>>>>>>rs.close
>>>>>>>>>set rs = nothing
>>>>>>>>>=========================================
>>>>>>>>
>>>>>>>> That worked. Thanks.
>>>>>>>>
>>>>>>>>>
>>>>>>>>>4) While in a code module, go to Tools | References. Uncheck
>>>>>>>>>Microsoft
>>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>>>checked,
>>>>>>>>>then scroll down the list, and check the highest numbered version of
>>>>>>>>>it.
>>>>>>>>>
>>>>>>>>>And that should do it.
>>>>>>>>>
>>>>>>>>>Neil
>>>>>>>>>
>>>>>>>>
>>>>>>>> (SNIP)
>>>>>>>
>>>>>
>>>
>

Re: Help needed with simple field problem (0/1)

No clue. I have no idea why the new record would be at the top of the list
when called from one place, but in the correct sort order when called from
another place. That makes absolutely no sense. Sorry I couldn't be more
helpful here.

wrote in message
news:o1eck3huc3rts4r52nk8322lc0qff99mff@4ax.com...
> Here's the combo's rowsource:
> SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER
> BY [Clients].[FullName];
>
> On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" wrote:
>
>>Where you place the requery command should have no effect on the sorting
>>of
>>the combo box. Is you combo explicitly sorted? If so, then this shouldn't
>>be
>>happening. If not, then there might be some weird glitch with a new record
>>being at the top, etc. Either way, just make sure you have an explicit
>>sort
>>in your combo's rowsource, and you should fine.
>>
>>
>> wrote in message
>>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com...
>>> Your right. I must have made a typo. There's a new problem. The new
>>> record is not sorted in the combo box, it's at the top of the list.
>>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
>>> is resorted. I did a couple tests, and this behavior is consistent.
>>> (I put the requery just before exit sub in the add record routine.)
>>>
>>>
>>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:
>>>
>>>>OK, we need to back up a second here. You said that the routine I sent
>>>>to
>>>>add a record was working. That routine referenced Me in the
>>>>Me.Recordsource=
>>>>item. But you write: "'Me' is not pointing to the form in the add record
>>>>routine." So, if "Me" is not pointing to the form in the add record
>>>>routine,
>>>>then how does the Me.Recordsource= line in the add record routine work?
>>>>If
>>>>Me works in one line of the routine, it will work in the other line of
>>>>the
>>>>same routine.
>>>>
>>>>So, I assume that the add record routine is in your button's OnClick
>>>>event,
>>>>and it resides in the form's code module, correct? If so, then Me will
>>>>work.
>>>>If you're not seeing Combo36, then it's either not in the form's code
>>>>module, or there's a compile error and that has to be resolved first.
>>>>Try
>>>>compiling and see what happens.
>>>>
>>>>
>>>> wrote in message
>>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>>>>
>>>>>>Well, if you changed the code to add records from a command button,
>>>>>>per
>>>>>>the
>>>>>>instructions I sent, then that would explain why it's not working from
>>>>>>the
>>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>>>>events
>>>>>>wouldn't apply.
>>>>>>
>>>>>>Just put the combo box requery code at the end of the routine that
>>>>>>adds
>>>>>>the
>>>>>>new record, and you should be fine.
>>>>>>
>>>>>
>>>>> I tried that, but I don't know how to reference it, and haven't gotten
>>>>> around to searching Google for that. "Me" is not pointing to the form
>>>>> in the add record routine. (There is no "Combo36" in the list after
>>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>>>>> get a basic book on using Access (just systax, I know how to program -
>>>>> I've also never used VB before). It took me about 30 minutes
>>>>> searching Google to find how to change a fields value... (you use the
>>>>> "clone"...) Do you know of a good/short online reference?
>>>>>
>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>>>>
>>>>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>>>>copy
>>>>>>>>and paste it here exactly.
>>>>>>>
>>>>>>> Row Source Type: Table/Query
>>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>>>>
>>>>>>> I am adding records from a control button on the form now.
>>>>>>>
>>>>>>> I tested this again, and now the combo box is updated with
>>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>>>>> not every time a new record is selected... I don't know why that
>>>>>>> works now. The combo box is still not updated with either
>>>>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>>>>
>>>>>>>>
>>>>>>>> wrote in message
>>>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil"
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> wrote in message
>>>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>>>>> (SNIP)
>>>>>>>>>>>
>>>>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>>>>> count
>>>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new
>>>>>>>>>>> record.
>>>>>>>>>>> When I do that the new record doesn't show up in the combo box
>>>>>>>>>>> list
>>>>>>>>>>> (the others still do). If I close the form and reopen it, the
>>>>>>>>>>> combo
>>>>>>>>>>> box sees the new record(s).
>>>>>>>>>>
>>>>>>>>>>Well, as John noted, just requery the combo box when a new record
>>>>>>>>>>is
>>>>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>>>>
>>>>>>>>>> Me.MyComboBox.Requery
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I've put that in three places (below). The only thing that updates
>>>>>>>>> the
>>>>>>>>> combo box is closing the form and reopening it.
>>>>>>>>>
>>>>>>>>> Private Sub Combo36_GotFocus()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>> Private Sub Form_AfterInsert()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> (SNIP)
>>>>>>>>>
>>>>>>>>>>The above is for if you prompt the user for the PK (customer ID?).
>>>>>>>>>>If
>>>>>>>>>>the
>>>>>>>>>>PK
>>>>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>>>>
>>>>>>>>>>=========================================
>>>>>>>>>>Dim rs As Recordset
>>>>>>>>>>
>>>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>>>>
>>>>>>>>>>rs.AddNew
>>>>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>>>>rs.Update
>>>>>>>>>>
>>>>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="&
>>>>>>>>>>rs!FieldPK
>>>>>>>>>>
>>>>>>>>>>rs.close
>>>>>>>>>>set rs = nothing
>>>>>>>>>>=========================================
>>>>>>>>>
>>>>>>>>> That worked. Thanks.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>4) While in a code module, go to Tools | References. Uncheck
>>>>>>>>>>Microsoft
>>>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>>>>checked,
>>>>>>>>>>then scroll down the list, and check the highest numbered version
>>>>>>>>>>of
>>>>>>>>>>it.
>>>>>>>>>>
>>>>>>>>>>And that should do it.
>>>>>>>>>>
>>>>>>>>>>Neil
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> (SNIP)
>>>>>>>>
>>>>>>
>>>>
>>

Re: Help needed with simple field problem (0/1)

Thanks for the links.

I wish one of them had a short section on globally accessing things
that don't happen to to pointed to my "Me."

I find myself working around that, regularly, and just looking for
solutions that don't need to access things outside of "Me." (I checked
the "ten commandments" to make sure I'm not doing something contrary
to the design of Access...)

With my current problem I can't think of a way around not seeing
outside of "Me." I have a check box on a form. If checked, I want the
date to be prefilled on a subform when new records are inserted there.
I can't see the default value propery of the date field in the subform
from the checkbox event on the main form. (I don't know how. I don't
see it under anything that comes up when you type 'me.') So I decided
to set the default value for the table (not the form) in the pop-up
date selector form (the button is on the main form). I get a "runtime
error 2465 - Client database can't find the field '|' referred to in
your expression." (There is no '|' in the expression...) The offending
code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess
you can't change default values in tables from within forms in
Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm
used to being able to easily reference anything in the known universe.
Another thing is I don't know how to reference things on the form that
called the popup from withing the popup. (Another reason I tried
changing the default table value. How do you get the selected date
back to the form?)

A short reference on globally referencing things (all in the same
page, not spread out over 50 pages) would be soooo usefull.

Any help appreciated.



On Mon, 19 Nov 2007 12:05:32 -0700, John W. Vinson
wrote:

>On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>
>>No, I'm not familiar with an online reference, though someone else here
>>might be. I would just go to my local Barnes and Noble or Borders and pick
>>up a beginning or intermediate Access programming book.
>
>Here are my (incomplete) list of useful online references...
>
>Jeff Conrad's resources page:
>http://www.accessmvp.com/JConrad/accessjunkie/resources.htm l
>
>The Access Web resources page:
>http://www.mvps.org/access/resources/index.html
>
>A free tutorial written by Crystal (MS Access MVP):
>http://allenbrowne.com/casu-22.html
>
>MVP Allen Browne's tutorials:
>http://allenbrowne.com/links.html#Tutorials
>
> John W. Vinson [MVP]

Re: Help needed with simple field problem (0/1)

(I'm reposting this as it never showed up on my newsfeed.)

Here's the combo's rowsource:
SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER
BY [Clients].[FullName];

On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" wrote:

>Where you place the requery command should have no effect on the sorting of
>the combo box. Is you combo explicitly sorted? If so, then this shouldn't be
>happening. If not, then there might be some weird glitch with a new record
>being at the top, etc. Either way, just make sure you have an explicit sort
>in your combo's rowsource, and you should fine.
>
>
> wrote in message
>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com...
>> Your right. I must have made a typo. There's a new problem. The new
>> record is not sorted in the combo box, it's at the top of the list.
>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
>> is resorted. I did a couple tests, and this behavior is consistent.
>> (I put the requery just before exit sub in the add record routine.)
>>
>>
>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:
>>
>>>OK, we need to back up a second here. You said that the routine I sent to
>>>add a record was working. That routine referenced Me in the
>>>Me.Recordsource=
>>>item. But you write: "'Me' is not pointing to the form in the add record
>>>routine." So, if "Me" is not pointing to the form in the add record
>>>routine,
>>>then how does the Me.Recordsource= line in the add record routine work? If
>>>Me works in one line of the routine, it will work in the other line of the
>>>same routine.
>>>
>>>So, I assume that the add record routine is in your button's OnClick
>>>event,
>>>and it resides in the form's code module, correct? If so, then Me will
>>>work.
>>>If you're not seeing Combo36, then it's either not in the form's code
>>>module, or there's a compile error and that has to be resolved first. Try
>>>compiling and see what happens.
>>>
>>>
>>> wrote in message
>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>>>
>>>>>Well, if you changed the code to add records from a command button, per
>>>>>the
>>>>>instructions I sent, then that would explain why it's not working from
>>>>>the
>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>>>events
>>>>>wouldn't apply.
>>>>>
>>>>>Just put the combo box requery code at the end of the routine that adds
>>>>>the
>>>>>new record, and you should be fine.
>>>>>
>>>>
>>>> I tried that, but I don't know how to reference it, and haven't gotten
>>>> around to searching Google for that. "Me" is not pointing to the form
>>>> in the add record routine. (There is no "Combo36" in the list after
>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>>>> get a basic book on using Access (just systax, I know how to program -
>>>> I've also never used VB before). It took me about 30 minutes
>>>> searching Google to find how to change a fields value... (you use the
>>>> "clone"...) Do you know of a good/short online reference?
>>>>
>>>>
>>>>>
>>>>> wrote in message
>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>>>
>>>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>>>copy
>>>>>>>and paste it here exactly.
>>>>>>
>>>>>> Row Source Type: Table/Query
>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>>>
>>>>>> I am adding records from a control button on the form now.
>>>>>>
>>>>>> I tested this again, and now the combo box is updated with
>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>>>> not every time a new record is selected... I don't know why that
>>>>>> works now. The combo box is still not updated with either
>>>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>>>
>>>>>>>
>>>>>>> wrote in message
>>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil"
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>> wrote in message
>>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>>>> (SNIP)
>>>>>>>>>>
>>>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>>>> count
>>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new record.
>>>>>>>>>> When I do that the new record doesn't show up in the combo box
>>>>>>>>>> list
>>>>>>>>>> (the others still do). If I close the form and reopen it, the
>>>>>>>>>> combo
>>>>>>>>>> box sees the new record(s).
>>>>>>>>>
>>>>>>>>>Well, as John noted, just requery the combo box when a new record is
>>>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>>>
>>>>>>>>> Me.MyComboBox.Requery
>>>>>>>>>
>>>>>>>>
>>>>>>>> I've put that in three places (below). The only thing that updates
>>>>>>>> the
>>>>>>>> combo box is closing the form and reopening it.
>>>>>>>>
>>>>>>>> Private Sub Combo36_GotFocus()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub Form_AfterInsert()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>> Me.Combo36.Requery
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>> (SNIP)
>>>>>>>>
>>>>>>>>>The above is for if you prompt the user for the PK (customer ID?).
>>>>>>>>>If
>>>>>>>>>the
>>>>>>>>>PK
>>>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>>>
>>>>>>>>>=========================================
>>>>>>>>>Dim rs As Recordset
>>>>>>>>>
>>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>>>
>>>>>>>>>rs.AddNew
>>>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>>>rs.Update
>>>>>>>>>
>>>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK
>>>>>>>>>
>>>>>>>>>rs.close
>>>>>>>>>set rs = nothing
>>>>>>>>>=========================================
>>>>>>>>
>>>>>>>> That worked. Thanks.
>>>>>>>>
>>>>>>>>>
>>>>>>>>>4) While in a code module, go to Tools | References. Uncheck
>>>>>>>>>Microsoft
>>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>>>checked,
>>>>>>>>>then scroll down the list, and check the highest numbered version of
>>>>>>>>>it.
>>>>>>>>>
>>>>>>>>>And that should do it.
>>>>>>>>>
>>>>>>>>>Neil
>>>>>>>>>
>>>>>>>>
>>>>>>>> (SNIP)
>>>>>>>
>>>>>
>>>
>

Re: Help needed with simple field problem (0/1)

The original showed up, and I replied to it. Here's my reply in case for
some reason you didn't see it:

No clue. I have no idea why the new record would be at the top of the list
when requery is called from one place, but in the correct sort order when
called from another place. That makes absolutely no sense. Sorry I couldn't
be more helpful here.


wrote in message
news:4g8dk312k85qrpj5r5k3mo2qu65itc8gut@4ax.com...
> (I'm reposting this as it never showed up on my newsfeed.)
>
> Here's the combo's rowsource:
> SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER
> BY [Clients].[FullName];
>
> On Wed, 21 Nov 2007 12:23:33 GMT, "Neil" wrote:
>
>>Where you place the requery command should have no effect on the sorting
>>of
>>the combo box. Is you combo explicitly sorted? If so, then this shouldn't
>>be
>>happening. If not, then there might be some weird glitch with a new record
>>being at the top, etc. Either way, just make sure you have an explicit
>>sort
>>in your combo's rowsource, and you should fine.
>>
>>
>> wrote in message
>>news:97o7k3t54hi9hljadr709mevkel6m31ic1@4ax.com...
>>> Your right. I must have made a typo. There's a new problem. The new
>>> record is not sorted in the combo box, it's at the top of the list.
>>> When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list
>>> is resorted. I did a couple tests, and this behavior is consistent.
>>> (I put the requery just before exit sub in the add record routine.)
>>>
>>>
>>> On Sun, 18 Nov 2007 23:50:09 -0600, "Neil" wrote:
>>>
>>>>OK, we need to back up a second here. You said that the routine I sent
>>>>to
>>>>add a record was working. That routine referenced Me in the
>>>>Me.Recordsource=
>>>>item. But you write: "'Me' is not pointing to the form in the add record
>>>>routine." So, if "Me" is not pointing to the form in the add record
>>>>routine,
>>>>then how does the Me.Recordsource= line in the add record routine work?
>>>>If
>>>>Me works in one line of the routine, it will work in the other line of
>>>>the
>>>>same routine.
>>>>
>>>>So, I assume that the add record routine is in your button's OnClick
>>>>event,
>>>>and it resides in the form's code module, correct? If so, then Me will
>>>>work.
>>>>If you're not seeing Combo36, then it's either not in the form's code
>>>>module, or there's a compile error and that has to be resolved first.
>>>>Try
>>>>compiling and see what happens.
>>>>
>>>>
>>>> wrote in message
>>>>news:t1e1k3dd2o1rbvnnetrq7859k4k4em8dpe@4ax.com...
>>>>> On Sun, 18 Nov 2007 13:28:56 GMT, "Neil" wrote:
>>>>>
>>>>>>Well, if you changed the code to add records from a command button,
>>>>>>per
>>>>>>the
>>>>>>instructions I sent, then that would explain why it's not working from
>>>>>>the
>>>>>>form's AfterUpdate or AfterInsert event: the form is not inserting the
>>>>>>record; you are, though the code. So the AfterUpdate and AfterInsert
>>>>>>events
>>>>>>wouldn't apply.
>>>>>>
>>>>>>Just put the combo box requery code at the end of the routine that
>>>>>>adds
>>>>>>the
>>>>>>new record, and you should be fine.
>>>>>>
>>>>>
>>>>> I tried that, but I don't know how to reference it, and haven't gotten
>>>>> around to searching Google for that. "Me" is not pointing to the form
>>>>> in the add record routine. (There is no "Combo36" in the list after
>>>>> you type "Me." - like there is in the AfterUpdate routine.) I need to
>>>>> get a basic book on using Access (just systax, I know how to program -
>>>>> I've also never used VB before). It took me about 30 minutes
>>>>> searching Google to find how to change a fields value... (you use the
>>>>> "clone"...) Do you know of a good/short online reference?
>>>>>
>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>news:nbpvj3dli4p572cnt4p4j61v6n22selnos@4ax.com...
>>>>>>> On Sun, 18 Nov 2007 02:07:45 GMT, "Neil" wrote:
>>>>>>>
>>>>>>>>Well, it should work. What's the rowsource of your combo box? Please
>>>>>>>>copy
>>>>>>>>and paste it here exactly.
>>>>>>>
>>>>>>> Row Source Type: Table/Query
>>>>>>> Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName],
>>>>>>> [Clients].[MiddleName], [Clients].[LastName] FROM Clients;
>>>>>>>
>>>>>>> I am adding records from a control button on the form now.
>>>>>>>
>>>>>>> I tested this again, and now the combo box is updated with
>>>>>>> Combo36_gotFocus(). I'd prefer to only update that when necessary,
>>>>>>> not every time a new record is selected... I don't know why that
>>>>>>> works now. The combo box is still not updated with either
>>>>>>> Form_AfterInsert() or Form_AfterUpdate().
>>>>>>>
>>>>>>>>
>>>>>>>> wrote in message
>>>>>>>>news:e6vuj39pv677lt8b21k2sd55uu9m2dp9rj@4ax.com...
>>>>>>>>> On Sat, 17 Nov 2007 04:51:36 -0600, "Neil"
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> wrote in message
>>>>>>>>>>news:1d8tj353gmctcceco124harbqdn2vn00fm@4ax.com...
>>>>>>>>> (SNIP)
>>>>>>>>>>>
>>>>>>>>>>> Once I use the combo box to select a record, the record selector
>>>>>>>>>>> count
>>>>>>>>>>> changes to 1. I can still arrow over to 2 and enter a new
>>>>>>>>>>> record.
>>>>>>>>>>> When I do that the new record doesn't show up in the combo box
>>>>>>>>>>> list
>>>>>>>>>>> (the others still do). If I close the form and reopen it, the
>>>>>>>>>>> combo
>>>>>>>>>>> box sees the new record(s).
>>>>>>>>>>
>>>>>>>>>>Well, as John noted, just requery the combo box when a new record
>>>>>>>>>>is
>>>>>>>>>>inserted (I'd do the After Insert event of the form). Just do:
>>>>>>>>>>
>>>>>>>>>> Me.MyComboBox.Requery
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I've put that in three places (below). The only thing that updates
>>>>>>>>> the
>>>>>>>>> combo box is closing the form and reopening it.
>>>>>>>>>
>>>>>>>>> Private Sub Combo36_GotFocus()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>> Private Sub Form_AfterInsert()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>> Private Sub Form_AfterUpdate()
>>>>>>>>> Me.Combo36.Requery
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> (SNIP)
>>>>>>>>>
>>>>>>>>>>The above is for if you prompt the user for the PK (customer ID?).
>>>>>>>>>>If
>>>>>>>>>>the
>>>>>>>>>>PK
>>>>>>>>>>is an autonumber PK, then you'd do this instead:
>>>>>>>>>>
>>>>>>>>>>=========================================
>>>>>>>>>>Dim rs As Recordset
>>>>>>>>>>
>>>>>>>>>>Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset)
>>>>>>>>>>
>>>>>>>>>>rs.AddNew
>>>>>>>>>>rs!OtherField = someothervalue 'This is optional.
>>>>>>>>>>rs.Update
>>>>>>>>>>
>>>>>>>>>>rs.bookmark = rs.LastModified
>>>>>>>>>>Me.Recordsource = "Select * From MyTable Where FieldPK="&
>>>>>>>>>>rs!FieldPK
>>>>>>>>>>
>>>>>>>>>>rs.close
>>>>>>>>>>set rs = nothing
>>>>>>>>>>=========================================
>>>>>>>>>
>>>>>>>>> That worked. Thanks.
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>4) While in a code module, go to Tools | References. Uncheck
>>>>>>>>>>Microsoft
>>>>>>>>>>ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not
>>>>>>>>>>checked,
>>>>>>>>>>then scroll down the list, and check the highest numbered version
>>>>>>>>>>of
>>>>>>>>>>it.
>>>>>>>>>>
>>>>>>>>>>And that should do it.
>>>>>>>>>>
>>>>>>>>>>Neil
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>> (SNIP)
>>>>>>>>
>>>>>>
>>>>
>>

Re: Help needed with simple field problem (0/1)

> With my current problem I can't think of a way around not seeing
> outside of "Me." I have a check box on a form. If checked, I want the
> date to be prefilled on a subform when new records are inserted there.
> I can't see the default value propery of the date field in the subform
> from the checkbox event on the main form. (I don't know how. I don't
> see it under anything that comes up when you type 'me.')

"Me" is just a shortcut. You don't have to use it. To reference a form (even
the form you're in) you can use the syntax Forms!Formname in place of Me
(where Formname is replaced with your formname).

To reference a subform from within the main form, you would use:
Me.Subformcontrolname.Form to reference the subform (you're basically
referencing the Form property of the subform control, which = the subform
itself). Using the above substitution for Me, if you prefer, you could
instead use Forms!Formname.Subformcontrolname.Form instead of the version
with Me.

You can also reference the main form from the subform, either by using the
Forms!formname syntax (which allows you to open any open form, including the
one containing the subform); or you can just use the Parent property of the
subform to reference the main form, as in: Me.Parent. (Note that though
Forms!formname allows you to reference any open form, you still have to use
the other sytax to reference an open subform).

So, with your above example, I would just reference the parent from the
subform. In the subform's BeforeInsert event, I would add:

If Me.Parent.CheckboxcontrolName Then
Me.Datecontrolname = VBA.Date
End If

If you instead want to set the default property for that field when the
check box is checked, you would just use the above syntax for a subform,
etc. But, for me personally, I prefer the above method of looking at the
check box state at the time the record is entered and just going from there.

> So I decided
> to set the default value for the table (not the form) in the pop-up
> date selector form (the button is on the main form). I get a "runtime
> error 2465 - Client database can't find the field '|' referred to in
> your expression." (There is no '|' in the expression...) The offending
> code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess
> you can't change default values in tables from within forms in
> Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm
> used to being able to easily reference anything in the known universe.

It's just a question of syntax. To change a default table value you'd have
to access the table's Tabledef in the Tabledefs collection and go from
there. But, obviously, that's not needed, and isn't the best way to go.

Really, I understand your frustration. But I think until you sit down and
systematically get up to speed with Access syntax and methodologies you're
going to continue to experience these frustrations. Every platform is
different, as you know. So, even just going through the basics of an intro
to VBA Access programming would be very helpful, you'd fine.

> Another thing is I don't know how to reference things on the form that
> called the popup from withing the popup. (Another reason I tried
> changing the default table value. How do you get the selected date
> back to the form?)

In the above you reference a subform; here you're discussing a pop-up.
They're not the same thing. A pop-up form (which is different than a dialog
box) is a standalone form, and you can reference it, or it can reference the
form that opened it, using the Forms!Formname syntax.

>
> A short reference on globally referencing things (all in the same
> page, not spread out over 50 pages) would be soooo usefull.

Hopefully the above will help.

Neil



>
> Any help appreciated.
>
>
>
> On Mon, 19 Nov 2007 12:05:32 -0700, John W. Vinson
> wrote:
>
>>On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>>
>>>No, I'm not familiar with an online reference, though someone else here
>>>might be. I would just go to my local Barnes and Noble or Borders and
>>>pick
>>>up a beginning or intermediate Access programming book.
>>
>>Here are my (incomplete) list of useful online references...
>>
>>Jeff Conrad's resources page:
>>http://www.accessmvp.com/JConrad/accessjunkie/resources.ht ml
>>
>>The Access Web resources page:
>>http://www.mvps.org/access/resources/index.html
>>
>>A free tutorial written by Crystal (MS Access MVP):
>>http://allenbrowne.com/casu-22.html
>>
>>MVP Allen Browne's tutorials:
>>http://allenbrowne.com/links.html#Tutorials
>>
>> John W. Vinson [MVP]

Re: Help needed with simple field problem (0/1)

On Fri, 23 Nov 2007 04:46:38 -0500, Doug@NoEmail.com wrote:

Me.NameOfSubformControl.Form!CourtDates.DefaultValue = Me.DateCtl.Value

>Thanks for the links.
>
>I wish one of them had a short section on globally accessing things
>that don't happen to to pointed to my "Me."
>
>I find myself working around that, regularly, and just looking for
>solutions that don't need to access things outside of "Me." (I checked
>the "ten commandments" to make sure I'm not doing something contrary
>to the design of Access...)
>
>With my current problem I can't think of a way around not seeing
>outside of "Me." I have a check box on a form. If checked, I want the
>date to be prefilled on a subform when new records are inserted there.
>I can't see the default value propery of the date field in the subform
>from the checkbox event on the main form. (I don't know how. I don't
>see it under anything that comes up when you type 'me.') So I decided
>to set the default value for the table (not the form) in the pop-up
>date selector form (the button is on the main form). I get a "runtime
>error 2465 - Client database can't find the field '|' referred to in
>your expression." (There is no '|' in the expression...) The offending
>code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess
>you can't change default values in tables from within forms in
>Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm
>used to being able to easily reference anything in the known universe.
>Another thing is I don't know how to reference things on the form that
>called the popup from withing the popup. (Another reason I tried
>changing the default table value. How do you get the selected date
>back to the form?)
>
>A short reference on globally referencing things (all in the same
>page, not spread out over 50 pages) would be soooo usefull.
>
>Any help appreciated.
>
>
>
>On Mon, 19 Nov 2007 12:05:32 -0700, John W. Vinson
> wrote:
>
>>On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>>
>>>No, I'm not familiar with an online reference, though someone else here
>>>might be. I would just go to my local Barnes and Noble or Borders and pick
>>>up a beginning or intermediate Access programming book.
>>
>>Here are my (incomplete) list of useful online references...
>>
>>Jeff Conrad's resources page:
>>http://www.accessmvp.com/JConrad/accessjunkie/resources.ht ml
>>
>>The Access Web resources page:
>>http://www.mvps.org/access/resources/index.html
>>
>>A free tutorial written by Crystal (MS Access MVP):
>>http://allenbrowne.com/casu-22.html
>>
>>MVP Allen Browne's tutorials:
>>http://allenbrowne.com/links.html#Tutorials
>>
>> John W. Vinson [MVP]

Wayne Gillespie
Gosford NSW Australia

Re: Help needed with simple field problem (0/1)

On Nov 23, 4:46 am, D...@NoEmail.com wrote:
> I wish one of them had a short section on globally accessing things
> that don't happen to to pointed to my "Me."

All forms, including subforms can be referenced with
Form_NameofForm,
if they have a module, or if they have their HasModule Property set to
True. I have used these pointers both personally, and in applications
which I have sold and which have run flawlessly thousands and
thousands of times.
The Access community here in CDMA, grudgingly admits that this might
work, but almost universally ignores it.
Possibly they get paid by the word, and love
Forms("ThisForm").Controls("TheSubFromControl").Forms("Nameo fSubForm").Controls("NameofSUbFormControl").Value .
Some even put in some "Bang" nonsense, a device designed to make the
syntax even more confusing, and to discourage learners from becoming
capable in programming.
Possibly they get laid when they can show their lover that they are
actually able to traverse this long passage of idiocy.
This is their right and yours as well.

I will continue to use Form_SuBFormName.ControlName.Value (or
BackColor or other Property).

Yes, this works with Report_ReportName, but with the caution that it's
difficult to change anything on a report while it's being rendered.

Re: Help needed with simple field problem (0/1)

"lyle" wrote in message
news:6627f5f1-3bb5-4030-8533-e6a1e5b45b1f@e10g2000prf.google groups.com...
> On Nov 23, 4:46 am, D...@NoEmail.com wrote:
>> I wish one of them had a short section on globally accessing things
>> that don't happen to to pointed to my "Me."
>
> All forms, including subforms can be referenced with
> Form_NameofForm,
> if they have a module, or if they have their HasModule Property set to
> True. I have used these pointers both personally, and in applications
> which I have sold and which have run flawlessly thousands and
> thousands of times.
> The Access community here in CDMA, grudgingly admits that this might
> work, but almost universally ignores it.
> Possibly they get paid by the word, and love
> Forms("ThisForm").Controls("TheSubFromControl").Forms("Nameo fSubForm").Controls("NameofSUbFormControl").Value
> .
> Some even put in some "Bang" nonsense, a device designed to make the
> syntax even more confusing, and to discourage learners from becoming
> capable in programming.
> Possibly they get laid when they can show their lover that they are
> actually able to traverse this long passage of idiocy.
> This is their right and yours as well.
>
> I will continue to use Form_SuBFormName.ControlName.Value (or
> BackColor or other Property).
>
> Yes, this works with Report_ReportName, but with the caution that it's
> difficult to change anything on a report while it's being rendered.

It is all well and good that you have found a syntax that works for you, but I
fail to see why you must assign queer motives or incompetence to anyone who uses
any other.

In a simple test...

?Forms!Form1.AllowFilters
True

?Form_Form1.AllowFilters
Run-time error '2467'
The expression you entered refers to an object that is closed or doesn't exist.

The form in question IS open and has a module. Admittedly, the following all do
work...

?Form_Form1.section(0).Height
?Form_Form1.Width
?Form_Form1.Controls.Count

Further testing reveals that the Form_FormName syntax does not work in Queries
whereas Forms!FormName does.

So it seems that the Form_FormName syntax *often* works, but not always. Just
my preference, but given a syntax that work most of the time versus one that
works all the time I prefer to use the latter even in specific situations where
the former would work. I simply see no reason for introducing an inconsistency.

In addition; within the context of these groups answering a question with the
Form_FormName syntax (even when it works) is likely to just raise additional
questions because of its non-familiarity whereas most people will recognize
Forms!FormName.

As for "paid by the word" ?Form_Form1.Controls.Count and
?Forms!Form1.Controls.Count differ by exactly one character. You were the one
to introduce into the conversation the more verbose Forms("FormName") variation
so that is a straw man. I fully concede the point when making references to
subforms. In that case Form_FormName is definitely fewer keystrokes, but one
can also consider the rare situation where a form could be used as a subform
more than once at the same time and I believe in that case the Form_FormName
syntax fails since it cannot distinguish between the multiple instances.

Finally, if there are people who use Forms!FormName simply because that is what
they are familiar with and because that is what they see in all of the help file
examples and in the majority of code snippets they see on the internet then just
what exactly is wrong with that? Are you basing the superiority of your
preference solely on keystroke count? Because it is more modern? Just because
I can cook scrambled eggs in a microwave yet still choose to do so in a frying
pan doesn't mean I am ignorant or dismissive of microwave technology. It might
just mean that I am used to cooking them in a pan and prefer doing it that way.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Help needed with simple field problem (0/1)

On Nov 23, 10:31 am, "Rick Brandt" wrote:

> ?Form_Form1.AllowFilters
> Run-time error '2467'
> The expression you entered refers to an object that is closed or doesn't exist.

A quick check here:

Sub temp()
Debug.Print Form_Employees.AllowFilters
End Sub

True is printed in the Immediate Window.

Re: Help needed with simple field problem (0/1)

lyle wrote:
> On Nov 23, 10:31 am, "Rick Brandt" wrote:
>
>> ?Form_Form1.AllowFilters
>> Run-time error '2467'
>> The expression you entered refers to an object that is closed or doesn't exist.
>
> A quick check here:
>
> Sub temp()
> Debug.Print Form_Employees.AllowFilters
> End Sub
>
> True is printed in the Immediate Window.

Suppose you have two instances of a form open
and want to refer to the controls on a form contained
in a subform control on the form. If you use Form_FormName
which instance of the form is being referenced?

Re: Help needed with simple field problem (0/1)

Thanks for the explanation.

I had thought parent might be right, but I didn't get a drop-down
after typing '...parent.' and the code wasn't working, and I didn't
know why. I was using the 'got focus' (and I tried 'on insert') event
in the subform, and then setting the default of the date field to the
date field on the main form. (Ignoring the check box for now.) That
doesn't work. The date field doesn't fill in. I changed it to the 'on
double click' event of the date field (setting the actual field and
not the default), and now it works.

My problem with referencing the form from the popup was I wasn't
including the 'Form!' in front of 'FormName!FieldName.'

I appreciate your help.


On Fri, 23 Nov 2007 07:48:47 -0600, "Neil" wrote:

>
>> With my current problem I can't think of a way around not seeing
>> outside of "Me." I have a check box on a form. If checked, I want the
>> date to be prefilled on a subform when new records are inserted there.
>> I can't see the default value propery of the date field in the subform
>> from the checkbox event on the main form. (I don't know how. I don't
>> see it under anything that comes up when you type 'me.')
>
>"Me" is just a shortcut. You don't have to use it. To reference a form (even
>the form you're in) you can use the syntax Forms!Formname in place of Me
>(where Formname is replaced with your formname).
>
>To reference a subform from within the main form, you would use:
>Me.Subformcontrolname.Form to reference the subform (you're basically
>referencing the Form property of the subform control, which = the subform
>itself). Using the above substitution for Me, if you prefer, you could
>instead use Forms!Formname.Subformcontrolname.Form instead of the version
>with Me.
>
>You can also reference the main form from the subform, either by using the
>Forms!formname syntax (which allows you to open any open form, including the
>one containing the subform); or you can just use the Parent property of the
>subform to reference the main form, as in: Me.Parent. (Note that though
>Forms!formname allows you to reference any open form, you still have to use
>the other sytax to reference an open subform).
>
>So, with your above example, I would just reference the parent from the
>subform. In the subform's BeforeInsert event, I would add:
>
>If Me.Parent.CheckboxcontrolName Then
> Me.Datecontrolname = VBA.Date
>End If
>
>If you instead want to set the default property for that field when the
>check box is checked, you would just use the above syntax for a subform,
>etc. But, for me personally, I prefer the above method of looking at the
>check box state at the time the record is entered and just going from there.
>
>> So I decided
>> to set the default value for the table (not the form) in the pop-up
>> date selector form (the button is on the main form). I get a "runtime
>> error 2465 - Client database can't find the field '|' referred to in
>> your expression." (There is no '|' in the expression...) The offending
>> code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess
>> you can't change default values in tables from within forms in
>> Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm
>> used to being able to easily reference anything in the known universe.
>
>It's just a question of syntax. To change a default table value you'd have
>to access the table's Tabledef in the Tabledefs collection and go from
>there. But, obviously, that's not needed, and isn't the best way to go.
>
>Really, I understand your frustration. But I think until you sit down and
>systematically get up to speed with Access syntax and methodologies you're
>going to continue to experience these frustrations. Every platform is
>different, as you know. So, even just going through the basics of an intro
>to VBA Access programming would be very helpful, you'd fine.
>
>> Another thing is I don't know how to reference things on the form that
>> called the popup from withing the popup. (Another reason I tried
>> changing the default table value. How do you get the selected date
>> back to the form?)
>
>In the above you reference a subform; here you're discussing a pop-up.
>They're not the same thing. A pop-up form (which is different than a dialog
>box) is a standalone form, and you can reference it, or it can reference the
>form that opened it, using the Forms!Formname syntax.
>
>>
>> A short reference on globally referencing things (all in the same
>> page, not spread out over 50 pages) would be soooo usefull.
>
>Hopefully the above will help.
>
>Neil
>
>
>
>>
>> Any help appreciated.
>>
>>
>>
>> On Mon, 19 Nov 2007 12:05:32 -0700, John W. Vinson
>> wrote:
>>
>>>On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>>>
>>>>No, I'm not familiar with an online reference, though someone else here
>>>>might be. I would just go to my local Barnes and Noble or Borders and
>>>>pick
>>>>up a beginning or intermediate Access programming book.
>>>
>>>Here are my (incomplete) list of useful online references...
>>>
>>>Jeff Conrad's resources page:
>>>http://www.accessmvp.com/JConrad/accessjunkie/resources.h tml
>>>
>>>The Access Web resources page:
>>>http://www.mvps.org/access/resources/index.html
>>>
>>>A free tutorial written by Crystal (MS Access MVP):
>>>http://allenbrowne.com/casu-22.html
>>>
>>>MVP Allen Browne's tutorials:
>>>http://allenbrowne.com/links.html#Tutorials
>>>
>>> John W. Vinson [MVP]
>

Re: Help needed with simple field problem (0/1)

wrote in message
news:9lgfk3lj5rn3fvr2mkhoeda4t6mfntdrt1@4ax.com...
> Thanks for the explanation.
>
> I had thought parent might be right, but I didn't get a drop-down
> after typing '...parent.'

Yes, you won't get the dropdown after parent, since it could be a form or a
report. If you absolutely want a dropdown (not saying you do, but if you
do), you can assign me.parent to a form object as follows:

Dim frm as form

set frm = me.parent

.... stuff...

set frm = nothing 'at the end

That would give you the dropdown. If you declare frm as the actual form
that's the parent, you'll also get references to the specific controls on
the form:

Dim frm as form_MyParentFormName

set frm = me.parent

....

Just fyi.


> and the code wasn't working, and I didn't
> know why. I was using the 'got focus' (and I tried 'on insert') event
> in the subform, and then setting the default of the date field to the
> date field on the main form. (Ignoring the check box for now.) That
> doesn't work.

Yes, because the default value of the field has to be set before the record
is added. When I said to use the BeforeInsert event of the subform, I said
to just set the field's value in that event, not set its default value.

> The date field doesn't fill in. I changed it to the 'on
> double click' event of the date field (setting the actual field and
> not the default), and now it works.

Right, because you're setting the actual field value. You could do that in
the BeforeInsert, instead, and save the double-click effort. Either way.

>
> My problem with referencing the form from the popup was I wasn't
> including the 'Form!' in front of 'FormName!FieldName.'
>
> I appreciate your help.

No problem. Glad you got it working.

Neil


>
>
> On Fri, 23 Nov 2007 07:48:47 -0600, "Neil" wrote:
>
>>
>>> With my current problem I can't think of a way around not seeing
>>> outside of "Me." I have a check box on a form. If checked, I want the
>>> date to be prefilled on a subform when new records are inserted there.
>>> I can't see the default value propery of the date field in the subform
>>> from the checkbox event on the main form. (I don't know how. I don't
>>> see it under anything that comes up when you type 'me.')
>>
>>"Me" is just a shortcut. You don't have to use it. To reference a form
>>(even
>>the form you're in) you can use the syntax Forms!Formname in place of Me
>>(where Formname is replaced with your formname).
>>
>>To reference a subform from within the main form, you would use:
>>Me.Subformcontrolname.Form to reference the subform (you're basically
>>referencing the Form property of the subform control, which = the subform
>>itself). Using the above substitution for Me, if you prefer, you could
>>instead use Forms!Formname.Subformcontrolname.Form instead of the version
>>with Me.
>>
>>You can also reference the main form from the subform, either by using the
>>Forms!formname syntax (which allows you to open any open form, including
>>the
>>one containing the subform); or you can just use the Parent property of
>>the
>>subform to reference the main form, as in: Me.Parent. (Note that though
>>Forms!formname allows you to reference any open form, you still have to
>>use
>>the other sytax to reference an open subform).
>>
>>So, with your above example, I would just reference the parent from the
>>subform. In the subform's BeforeInsert event, I would add:
>>
>>If Me.Parent.CheckboxcontrolName Then
>> Me.Datecontrolname = VBA.Date
>>End If
>>
>>If you instead want to set the default property for that field when the
>>check box is checked, you would just use the above syntax for a subform,
>>etc. But, for me personally, I prefer the above method of looking at the
>>check box state at the time the record is entered and just going from
>>there.
>>
>>> So I decided
>>> to set the default value for the table (not the form) in the pop-up
>>> date selector form (the button is on the main form). I get a "runtime
>>> error 2465 - Client database can't find the field '|' referred to in
>>> your expression." (There is no '|' in the expression...) The offending
>>> code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess
>>> you can't change default values in tables from within forms in
>>> Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm
>>> used to being able to easily reference anything in the known universe.
>>
>>It's just a question of syntax. To change a default table value you'd have
>>to access the table's Tabledef in the Tabledefs collection and go from
>>there. But, obviously, that's not needed, and isn't the best way to go.
>>
>>Really, I understand your frustration. But I think until you sit down and
>>systematically get up to speed with Access syntax and methodologies you're
>>going to continue to experience these frustrations. Every platform is
>>different, as you know. So, even just going through the basics of an intro
>>to VBA Access programming would be very helpful, you'd fine.
>>
>>> Another thing is I don't know how to reference things on the form that
>>> called the popup from withing the popup. (Another reason I tried
>>> changing the default table value. How do you get the selected date
>>> back to the form?)
>>
>>In the above you reference a subform; here you're discussing a pop-up.
>>They're not the same thing. A pop-up form (which is different than a
>>dialog
>>box) is a standalone form, and you can reference it, or it can reference
>>the
>>form that opened it, using the Forms!Formname syntax.
>>
>>>
>>> A short reference on globally referencing things (all in the same
>>> page, not spread out over 50 pages) would be soooo usefull.
>>
>>Hopefully the above will help.
>>
>>Neil
>>
>>
>>
>>>
>>> Any help appreciated.
>>>
>>>
>>>
>>> On Mon, 19 Nov 2007 12:05:32 -0700, John W. Vinson
>>> wrote:
>>>
>>>>On Sun, 18 Nov 2007 23:52:13 -0600, "Neil" wrote:
>>>>
>>>>>No, I'm not familiar with an online reference, though someone else here
>>>>>might be. I would just go to my local Barnes and Noble or Borders and
>>>>>pick
>>>>>up a beginning or intermediate Access programming book.
>>>>
>>>>Here are my (incomplete) list of useful online references...
>>>>
>>>>Jeff Conrad's resources page:
>>>>http://www.accessmvp.com/JConrad/accessjunkie/resources. html
>>>>
>>>>The Access Web resources page:
>>>>http://www.mvps.org/access/resources/index.html
>>>>
>>>>A free tutorial written by Crystal (MS Access MVP):
>>>>http://allenbrowne.com/casu-22.html
>>>>
>>>>MVP Allen Browne's tutorials:
>>>>http://allenbrowne.com/links.html#Tutorials
>>>>
>>>> John W. Vinson [MVP]
>>

Re: Help needed with simple field problem (0/1)

On Nov 23, 5:35 pm, rkc wrote:
> lyle wrote:
> > On Nov 23, 10:31 am, "Rick Brandt" wrote:
>
> >> ?Form_Form1.AllowFilters
> >> Run-time error '2467'
> >> The expression you entered refers to an object that is closed or doesn't exist.
>
> > A quick check here:
>
> > Sub temp()
> > Debug.Print Form_Employees.AllowFilters
> > End Sub
>
> > True is printed in the Immediate Window.
>
> Suppose you have two instances of a form open
> and want to refer to the controls on a form contained
> in a subform control on the form. If you use Form_FormName
> which instance of the form is being referenced?

I don't know. Sometime when I consider it either worthwhile to have
two instances of a form open (with the form having a subform), or
likely that it will happen I'll check into it.
How is done using the [long idiotic string from hell] syntax?

I generally don't use subforms any more as the use of the syntax I
have suggested facilitates using full forms as sub forms ... with none
of the limitations of subforms.

When I use multiple instances of forms it is through code like:

Dim ADetailForms(0 To 1) As [Form_Faculty Details]
Public Sub OpenSomeFormInstances()
Dim z As Long
For z = 0 To 1
Set ADetailForms(z) = New [Form_Faculty Details]
With ADetailForms(z)
.Visible = True
.Caption = "Look Ma Multiple Distinguishable Instances of
a Form " & z
End With
Next z
End Sub

Public Sub ZapAllThoseFormInstances()
Erase ADetailForms
End Sub

Re: Help needed with simple field problem (0/1)

lyle wrote:
> On Nov 23, 5:35 pm, rkc wrote:
>
>> Suppose you have two instances of a form open
>> and want to refer to the controls on a form contained
>> in a subform control on the form. If you use Form_FormName
>> which instance of the form is being referenced?
>
> I don't know. Sometime when I consider it either worthwhile to have
> two instances of a form open (with the form having a subform), or
> likely that it will happen I'll check into it.

That'll be great.

Re: Help needed with simple field problem (0/1)

Doug@NoEmail.com wrote in
news:td4dk3hbjljdf1pn052a34ar8b20rpvumi@4ax.com:

> I wish one of them had a short section on globally accessing
> things that don't happen to to pointed to my "Me."

"Me" is a reference to the CLASS MODULE of the object the code is
in. If you want to refer to a different object, you have to specify
it completely. Another form will be Form!frmOtherForm in place of
Me. A form that is embedded in the form you're coding in will be
Me!subFormControlName.Form.

That's all you need to know.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Help needed with simple field problem (0/1)

lyle wrote in
news:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googleg roups.com
:

> I generally don't use subforms any more as the use of the syntax I
> have suggested facilitates using full forms as sub forms ... with
> none of the limitations of subforms.

What are the limitations of subforms?

> When I use multiple instances of forms it is through code like:

How do you use the form module-name syntax (which is what
Forms_FormName is) to refer to those different instances? It's
pretty easy to use the form's collection to figure out which one you
want to use by checking the caption. But how do you do that with
your suggested syntax?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Help needed with simple field problem (0/1)

lyle wrote in
news:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googleg roups.com
:

> Sometime when I consider it either worthwhile to have
> two instances of a form open (with the form having a subform), or
> likely that it will happen I'll check into it.

Huh? What about a single form with two copies of the same subform?
How do you distinguish those two subforms with your suggested
syntax? Adding a second form with a subform doesn't change the
problem space at all, though it might very well be more common.

I can't believe your applications have such poor UI that you don't
use subforms or that you're such a poor programmer that you'd not
re-use a form in more than one location if it were appropriate (as
it very often is).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Help needed with simple field problem (0/1)

On Nov 24, 7:46 pm, "David W. Fenton"
wrote:
> D...@NoEmail.com wrote innews:td4dk3hbjljdf1pn052a34ar8b20rpvumi@4ax.com:

> "Me" is a reference to the CLASS MODULE of the object the code is
> in.

Private Sub Form_Load()
Debug.Print Me Is Me.Module 'False
Debug.Print TypeName(Me), TypeName(Me.Module) 'Form_Issue List, Module
Debug.Print VarPtr(Me), VarPtr(Me.Module) '1297044,1296972
Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776
End Sub

Re: Help needed with simple field problem (0/1)

On Nov 24, 7:46 pm, "David W. Fenton"
wrote:

> "Me" is a reference to the CLASS MODULE of the object the code is
> in.

Private Sub Form_Load()
Debug.Print Me Is Me.Module 'False
Debug.Print TypeName(Me), TypeName(Me.Module) 'Form_Issue List, Module
Debug.Print VarPtr(Me), VarPtr(Me.Module) '1297044,1296972
Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776
End Sub

Re: Help needed with simple field problem (0/1)

On Nov 24, 7:51 pm, "David W. Fenton"
wrote:
> lyle wrote innews:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googl egroups.com
> :
>
> > Sometime when I consider it either worthwhile to have
> > two instances of a form open (with the form having a subform), or
> > likely that it will happen I'll check into it.
>
> Huh? What about a single form with two copies of the same subform?

TTBOMK VBA will search down the stack, so to speak, for the first
pointer to the subform; thus it will reference the last created
instance of the subform.

Re: Help needed with simple field problem (0/1)

On Nov 24, 7:51 pm, "David W. Fenton"
wrote:

> I can't believe your applications have such poor UI that you don't
> use subforms or that you're such a poor programmer that you'd not
> re-use a form in more than one location if it were appropriate (as
> it very often is).

I can't think of any case where I have used the same form twice as a
subform (on the same parent form) so I can't say how I would deal with
that, but I expect it would be smoothly and efficiently, if I used
subforms, that is, but, of course, I don't. I think I stopped using
subforms five years ago or so; as I roll my own, users do not seem to
notice, except that they like to be able to move and size the "sub"
form.
I'm not sure if you were a regular here in CDMA when I posted in more
than one thread about exposing forms in library mdbs, making them
available for reuse not only within one application, but within many
at the same time. That must be almost ten years ago.
I really hope you won't bother yourself with my level of programming
expertise. It's been of sufficient quality over the past twenty-five
years that I have not had to seek clients actively during that time.
No need for you to miss your beauty sleep worrying about that.

I don't really expect to convert anyone to my position on this matter,
David, and arguing is tedious. So I'm checking out of this thread now.

Re: Help needed with simple field problem (0/1)

David W. Fenton wrote:
> Doug@NoEmail.com wrote in
> news:td4dk3hbjljdf1pn052a34ar8b20rpvumi@4ax.com:
>
>> I wish one of them had a short section on globally accessing
>> things that don't happen to to pointed to my "Me."
>
> "Me" is a reference to the CLASS MODULE of the object the code is
> in. If you want to refer to a different object, you have to specify
> it completely. Another form will be Form!frmOtherForm in place of
> Me. A form that is embedded in the form you're coding in will be
> Me!subFormControlName.Form.
>
> That's all you need to know.
>

I would say Me is a way to reference an instance of an object
from within the class module that defines the object. It's all
a bit fuzzy in Access since so much of the implementation of the
objects defined by the application is hidden.

Re: Help needed with simple field problem (0/1)

lyle wrote:

>
> I don't really expect to convert anyone to my position on this matter,
> David, and arguing is tedious. So I'm checking out of this thread now.

You're efforts are not wasted, Sir Lyle of Fairfield.
I learned of this from you previously, have investigated it
further, and am grateful to you for having enlightened me.

I just don't use it.

Take from that what you will.

Re: Help needed with simple field problem (0/1)

"lyle" wrote in message
news:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googleg roups.com...
> When I use multiple instances of forms it is through code like:
>
> Dim ADetailForms(0 To 1) As [Form_Faculty Details]
> Public Sub OpenSomeFormInstances()
> Dim z As Long
> For z = 0 To 1
> Set ADetailForms(z) = New [Form_Faculty Details]
> With ADetailForms(z)
> .Visible = True
> .Caption = "Look Ma Multiple Distinguishable Instances of
> a Form " & z
> End With
> Next z
> End Sub
>
> Public Sub ZapAllThoseFormInstances()
> Erase ADetailForms
> End Sub
>

Hi, Lyle. I responded to this in a new thread I started here called "Working
With Multiple Form Instances." If you could take a look at it, I'd
appreciate it. Thanks!

Neil

Re: Help needed with simple field problem (0/1)

lyle wrote in
news:00dfcec4-498e-421a-b169-cc7288e0f925@a39g2000pre.google groups.co
m:

> On Nov 24, 7:46 pm, "David W. Fenton"
> wrote:
>> D...@NoEmail.com wrote
>> innews:td4dk3hbjljdf1pn052a34ar8b20rpvumi@4ax.com:
>
>> "Me" is a reference to the CLASS MODULE of the object the code is
>> in.
>
> Private Sub Form_Load()
> Debug.Print Me Is Me.Module 'False
> Debug.Print TypeName(Me), TypeName(Me.Module) 'Form_Issue List,
> Module Debug.Print VarPtr(Me), VarPtr(Me.Module) '1297044,1296972
> Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776
> End Sub

Me will be of type FORM when it's a form's class module, of type
REPORT when a report, and of type CLASS MODULE when in a standalone
class module. But this is because someone decided that the class
module reference should return an object of the type it is is part
of (or, put another way, of the subtype of class module).

It's very helpful, I think, to remember that it's the class module
that is referred to by Me simply because otherwise, it's hard to
explain why it works in a standalone class module but not in a
regular code module.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Help needed with simple field problem (0/1)

lyle wrote in
news:daba940c-e3fc-4601-86af-ee995c5d09b3@a39g2000pre.google groups.co
m:

> On Nov 24, 7:51 pm, "David W. Fenton"
> wrote:
>> lyle wrote
>> innews:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googl egroup
>> s.com
>> :
>>
>> > Sometime when I consider it either worthwhile to have
>> > two instances of a form open (with the form having a subform),
>> > or likely that it will happen I'll check into it.
>>
>> Huh? What about a single form with two copies of the same
>> subform?
>
> TTBOMK VBA will search down the stack, so to speak, for the first
> pointer to the subform; thus it will reference the last created
> instance of the subform.

Yes, and then what? How do you utilize your syntax and act upon
something other than the default that is returned?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Help needed with simple field problem (0/1)

lyle wrote in
news:3d620d40-7292-4802-9fa0-b639ce77831d@o6g2000hsd.googleg roups.com
:

> On Nov 24, 7:51 pm, "David W. Fenton"
> wrote:
>
>> I can't believe your applications have such poor UI that you
>> don't use subforms or that you're such a poor programmer that
>> you'd not re-use a form in more than one location if it were
>> appropriate (as it very often is).
>
> I can't think of any case where I have used the same form twice as
> a subform (on the same parent form)

I use it all the time for merging records during data cleanup
projects. That's both in my own maintenance work for clients as well
as in the applications my clients use (which very often need a
record merger form). Here's a screen shot:

http://dfenton.com/DFA/examples/DuplicateResolution.png

[that's actually a very early version of the design, so it's got
problems, but that was what was handy without logging onto a
terminal server and doing a screen capture there]

> so I can't say how I would deal with
> that, but I expect it would be smoothly and efficiently, if I used
> subforms, that is, but, of course, I don't.

How do you use your syntax to refer to the two subforms on the
example form above? How do you distinguish the left one from the
right one? I do it because the subform controls on the form have
different names, delineating the difference between their functions.

> I think I stopped using
> subforms five years ago or so; as I roll my own, users do not seem
> to notice, except that they like to be able to move and size the
> "sub" form.

How would you implement a record merger form like my example?

> I'm not sure if you were a regular here in CDMA when I posted in
> more than one thread about exposing forms in library mdbs, making
> them available for reuse not only within one application, but
> within many at the same time.

Yes. I think it's an excellent idea.

> That must be almost ten years ago.
> I really hope you won't bother yourself with my level of
> programming expertise. It's been of sufficient quality over the
> past twenty-five years that I have not had to seek clients
> actively during that time. No need for you to miss your beauty
> sleep worrying about that.

I think not using subforms is pig-headed and stupid, and costs your
clients a lot of money that they shouldn't have to pay.

> I don't really expect to convert anyone to my position on this
> matter, David, and arguing is tedious. So I'm checking out of this
> thread now.

I'd like to hear some kind of rational explanation of why you choose
to torture Access by avoiding its most powerful feature, i.e., the
subform (particularly the continuous one).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/