Bookmark record number

[Guest]

I am using Access 2003/Win xp pro/ SQL Server 2000

I have a form in my access project that doesn't seem to work properly. I am
using a combo box to select a job/shipping address and would like it to fill
the fields once the job/ shipping address is selected. The only way I found
for it to populate the fields is to do a requery or refresh. If I do this
the record count goes back to the first record. I would like for it to stay
on the current record.

I have set a variable equal to the bookmark property before I update the
combo box and then after the combo box updates I set the bookmark property to
my variable. For some reason, when I add a new record with this code it goes
to the previous record. For example, if I am adding record "10" and insert a
job/shipping address after the before and after update events of the combo
box fire, I end up on record "9". The bookmark seems to be working if I
change the job/shipping address. I only have a problem with insert new
records. I have also tried using the goto record command for the Primary
key, but I get error messages telling me this command is not available.

Any help is greatly appreciated..

 

[Allen Browne]

There are a couple of ways to do this.

Open the Northwind sample database, the Orders form. It is based on a query
that reads from both the Orders table and the Customers table. As a result,
the customer's current address fields are there in the form's recordsource,
so the AfterUpdate event of the CustomerID combo reads those fields and
assigns them to the shipping address.

If you can't use that approach, you could use the AfterUpdate event of your
combo to OpenRecordset on the address table, and assign the values that way.

Bookmarks last only as long as the recordset. When you Requery, the
recordset is recreated, so the old bookmarks are no longer valid. If you
really did have to take that approach (not recommended), you would need to
save the primary key value into a variable, and then FindFirst that value
after the reqeury.

There is another (probably unrelated) bug in Access where it reports a
spurious bookmark when you are at a new record and have begun entry. The new
record does not have a bookmark, but Access wrongly reports the bookmark of
the most recently visited record. It has been doing this wrongly for 11
years, so I doubt we can get MS to fix it now.

 

[Guest]

Allen,

I attempted to the first solution, but I was unsuccessful. I would like to
try the find first command and have it look for my shipping addressid, but I
do not have access to that step of the recordsetclone command. It may be
because I am using an Access Project.

I did try dlookup of the address attributes based on the shipping address
ID, but it only changed my field if I selected the record twice. I noticed
the orders query in northwind, but for some reason my forms' address fields
do not automatically update. I noticed that they do update the table and
subsequent query, but they just don't follow through on the form unless I
refresh or requery or select my combo box entry twice.

Any further advice would be greatly appreciated.

Mark

 

[Allen Browne]

In the Northwind example, the Orders form is based on [Orders Qry].
This query contains the fields from the Orders table (including the shipping
address fields), and also from the Customers table (including the customer's
usual address fields.)

On the form, the AfterUpdate event of the CustomerID combo then assigns the
fields from the customer's normal address to the shipping address fields. So
what happens is that as soon as you select a customer in the combo, Access
has all the ususl address fields available from the Customers table and you
can therefore just assign them to the shipping address fields.

This is the simplest and most efficient solution you could try. You can
start with just one line of code to assign one of the address fields. Once
you get that working you can add another line to assign the 2nd field.