Storing looked-up combo field value for use in subsequently added records

[obembe]

I'm creating a database for my coin collection. I want to enter the data by
country, e.g. all coins for Italy, etc.

My two main tables are:-

tblCountry: pkCountryNo (autonumber); CountryName (text)

tblCoin: pkCoinNo (auto); fkCountryNo (long integer); etc.

My Coin form with lookup to countries works fine.
However, I want to create a (vertical) form with an initial lookup to
country (probably in the header?), so that the country I choose, e.g. Italy,
is displayed then stored.
If any coins exist for Italy, the first one will be displayed in the form.
Any coins I subsequently add will inherit the the looked-up country and be
updated behind the scenes. I won't make the country field visible in the
coin form, as it'll be apparent from the country lookup field what I'm
viewing/entering. Any ideas on how I would achieve this in the simplest way?

Currently I'm just doing this automatically in a datasheet with subforms -
but the same functionality in a vertical form would be much nicer.

Regards,

Adetola Obembe.

 

[Arvin Meyer]

Add a textbox on your form, bound to the Country field in your table. Set
the form's Current event and the AfterUpdate event of the unbound combobox
to put the date in the Country text box (air code):

Sub Form_Current()
If Len(Me.txtCountry & vbNullString) = 0 Then
Me.txtCountry = Me.cboCountry
End If
End Sub

When opening to an existing record, nothing is changed When opening to a new
record, for the first instance, everything is blank. On the subsequent
records if cboCountry combo has a value, the txtCountry textbox fills in. If
the combo is changed:

Sub cboCountry_AfterUpdate()
Me.txtCountry = Me.cboCountry
End Sub

the new value is added.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

I'm creating a database for my coin collection. I want to enter the data by
country, e.g. all coins for Italy, etc.

My two main tables are:-

tblCountry: pkCountryNo (autonumber); CountryName (text)

tblCoin: pkCoinNo (auto); fkCountryNo (long integer); etc.

My Coin form with lookup to countries works fine.
However, I want to create a (vertical) form with an initial lookup to
country (probably in the header?), so that the country I choose, e.g. Italy,
is displayed then stored.

I'd suggest changing the structure of your two tables to do away with
the "Lookup". All a Lookup field does is cause problems - it conceals
the actual contents of your table, and causes no end of confusion.
Since country names are unique and won't change, at least not for this
purpose (if you have USSR Rubles they're still USSR coins even though
that country no longer exists), I'd suggest that tblCountry have only
ONE field, the country name, as its Primary Key; tblCoins would then
have a CountryName field. On your Form you would use a Combo Box based
on tblCountry to display and enter the country name.

If any coins exist for Italy, the first one will be displayed in the form.
Any coins I subsequently add will inherit the the looked-up country and be
updated behind the scenes. I won't make the country field visible in the
coin form, as it'll be apparent from the country lookup field what I'm
viewing/entering. Any ideas on how I would achieve this in the simplest way?

Currently I'm just doing this automatically in a datasheet with subforms -
but the same functionality in a vertical form would be much nicer.

You could have a Form based on tblCountry, with a Subform based on
tblCoins, using CountryName as the master/child link field; or (with
your current structure) use pkCountryNo as the Master Link Field and
fkCountryNo as the Child Link Field. Make the main form a single form
and the subform a continuous form to get the "vertical" arrangement.


John W. Vinson[MVP]

 

[obembe]

Hello John,

I eventually changed my table layout as you suggested, but most reluctantly,
as Arvin's suggestion almost worked - after choosing country name from my
combo box, it brought up related records for the country number MINUS ONE!
But changing the country primary key to name fixed that. Using up that extra
space (lots of text rather than numbers) in child records doesn't mater on
my home PC, but in a live situation surely it could cause a big space issue?

Anyway, thanks again.

Regards,

Adetola.

 

[obembe]

Hello Arvin,

Thanks for your help. As I explained to John below

after choosing country name from my combo box, it brought up related

records for the country number MINUS ONE!

Changing my primary key on country from number to name, as John suggested,
did the trick, though I was annoyed to have to do it.

Obviously I made some error somewhere, as your code basically worked - but
have you any idea what error I made
to get my combo-selected record number out of step by one with the results
(e.g. if I chose country 2, Albania, I got coin records for Afghanistan,
country 1)?

Anyway, I'm a novice and must expect trial and error, as it's all a good
learning experience.

Thanks again,

Regards,

Adetola.

 

[John Vinson]

Hello John,

I eventually changed my table layout as you suggested, but most reluctantly,
as Arvin's suggestion almost worked - after choosing country name from my
combo box, it brought up related records for the country number MINUS ONE!
But changing the country primary key to name fixed that. Using up that extra
space (lots of text rather than numbers) in child records doesn't mater on
my home PC, but in a live situation surely it could cause a big space issue?

Not really. You're limited to two GIGABYTES in a database (and disk
costs about a penny a megabyte these days); if the average length of a
country name is 8-10 bytes that's not that much more than the 4 bytes
of a long integer - and you're saving 4x(number of countries) bytes in
the country table and in its indexes.


John W. Vinson[MVP]

 

[Arvin Meyer]

I agree with John, but I'm puzzled as to why you are getting the wrong
result from my code. Can you post the output of the country combo box sql? I
would only need to see the first half dozen or so records and the sql
statement.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[obembe]

Hello Arvin,

Unfortunately I didn't save the attempt, as changing the country table key
and using form and subform got rid of the need for the special code you gave
me. However, the situation was that my first 6 country records were:-

Auto-number Country
1 Afghanistan
2 Albania
3 Alderney
4 Algeria
5 Andorra
6 Angola

I had coins for Albania, Algeria and Andorra - none for the other countries.
(OK, Alderney isn't a country, but it issues legal tender coins). When I
selected Albania from the country combo box, I got nothing in my coin form -
when I added a text box to display the foreign key in the coin form, it was
shown to be 1. If I clicked on Alderney in the combo box, the coins for
Albania would be displayed.

I'm a real novice with Access, but I'm wondering - do combo lists start from
zero? And if so, was the list value getting transferred to my foreign key
instead of the country auto-number? Depending on what I did, the country
name of Afghanistan sometimes even got changed to Albania in the country
table, even though I was only enquiring on country (so I thought) - so I
ended up with 2 Albanias, only one of them with coins.

However, after re-keying on country name in both parent and child tables and
deleting country number, my problems disappeared. At some future point (when
I'm hopefully more proficient) I'm going to want to use auto-number keys
instead of text - so I've kept your combo box event suggestions in a Word
doc. Only then will I get an inkling of what went wrong.

Regards,

Adetola.

 

[Arvin Meyer]

I'm a real novice with Access, but I'm wondering - do combo lists start from
zero? And if so, was the list value getting transferred to my foreign key
instead of the country auto-number? Depending on what I did, the country
name of Afghanistan sometimes even got changed to Albania in the country
table, even though I was only enquiring on country (so I thought) - so I
ended up with 2 Albanias, only one of them with coins.

The index of columns in a combobox starts at zero, but that isn't the
problem. I just created the tables and a form in a test db and can't
reproduce your results. It's a shame you no longer have the same data set up
or I'd ask for your database to determine where the error is.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[obembe]

The index of columns in a combobox starts at zero

Thanks, worth remembering...

but that isn't the problem. I just created the tables and a form in a test db and can't
reproduce your results. It's a shame you no longer have the same data set up
or I'd ask for your database to determine where the error is.

I'm famous for creating impossible errors when I first learn a new app.
Somehow I can't have fully grasped your solution or else got some other part
wrong. I generally turn out to be a good j-curver though.

Regards,

Adetola.