Update a bound control

[Peter Bellamy]

I have a form based on a query that is a view of two tables.
One table is a register of 1000 numbers (barcodes)
The other is a of parts, with their associated details.
The tables are related by the 'part number'

The form allows the user to take the next available barcode number and writes the part number to the register.

All that works fine.
There is a bound control on the form to show the associated barcode number and what does not work is the form does not show the barcode number that has just been selected!

I have tried Requery and all that does is take the form to the first record, stepping through to the one just updated, the bound control still shows blank.

Closeing and opening the form the number is shown!

If I force the control to be updated by writing some VBA code for it, it updates but complains of a duplicates when the form is closed.

Any ideas??
Cheers
Peter

 

[modest]

Me.Refresh

 

[Peter Bellamy]

Tried and it did not do anything !!

 

[Peter Bellamy]

Has this defeated everyone ???

Peter

 

[boblarson]

I might suggest posting a stripped down version of your database here for us to look at.

 

[Peter Bellamy]

Following your suggestion please find attached a stripped down version of my code. It contains the 3 required tables, a query and a form.

The issue is with the Assign Barcode button.
The code behind it has been written for my db which is a split front end, back end type. This is why there is a path in the Recordset statements. You will have to alter it to suit where you have saved it.

The problem is that the newly assigned barcode does not appear on the form, unless it is closed and opened again.

I would welcome all suggestions !!
Cheers

 

[boblarson]

Gotta ask this question then. Why are you connecting via code and not by using linked tables?

 

[Peter Bellamy]

Sorry, did not make it clear.
Yes using linked tables between front end and db.

Only using Recordset etc to find the line in the table I want and to update it.

Hope that makes sense!

 

[boblarson]

Then -

1. bind the barcode number text box to the barcode field in your form's recordsource.

2. You don't need:
Set BcodeDb = DBEngine.Workspaces(0).OpenDatabase("Z:\Database\2000\Service2k.mdb")
If you must use DAO to insert the value (which if you had the field bound you would only have to set the text box equal to the created value and wouldn't need this code completely), use the connection of:
Set BcodeDb = CurrentDb

But it is the fact that your text box is not bound as to why it doesn't show up again.

 

[boblarson]

Okay, I just noticed that you had TWO fields for barcode, one bound, one unbound. If you want the second one to show the stuff after adding the first, set the code:
Me.bcode = BcodeRs!barcode_barcode
in the form's On Current event so that it displays the bound value.

 

[Peter Bellamy]

Bob,
Thanks for your input.

The form started life as just a view on the Parts Table, hence the Recordset lines of code.
I modified it to a query on both tables when I had problems. I agree it can now be simpler

The control I want to respond after update is parts_barcode, which is bound to barcode_barcode.

The other unbound control was just to see what was going on.

If I 'force' parts_barcode to show the new barcode, the form errors with 'duplicate value' when it is closed.