Newbie: Auto-Fill record from Lookup (1 Viewer)

[Chromedome]

Hi Everyone
We are a smallish Telecoms company in UK. We originally wrote our current database in objectPAL some 10 years ago which has run the company 1st class

However, we have decided to redesign the whole thing in Access. Things are going OK but I can't seem to find an answer to the following solution with regard to Lookup Tables:

In Paradox when setting up a lookup Table you could choose simple Lookup or 'Lookup & Fill' which would copy and fill-in the relevant fields (eg: StockCode, ProductCode, Description, ListPrice, CostPrice etc.) from the Lookup Record ie: from Product List into LineItem.

I'm sure there must be something just as simple in Access without reverting to code. No objection to writing code but why re-invent the wheel if the function is already built-in.

Hope you can help and I look forward to your reply

Regards

CD (UK)

 

[neileg]

I still get a bit misty eyed about Paradox. It handled many things better than Access.

I assume you will use a combo to pick the product. You can, in the after update event of the combo, set code to populate your other controls from the relevant columns in the combo.

 

[Chromedome]

Thanksfor your speedy reply Neil
Yes, I thought I would use a Combo to pick the product. In Paradox I used both the [CTRL+Space] to pull the Look-up or a Query under a button to narrow the criteria. Seriously, Is there no standard procedure to pull the required fields in? I thought this would be a basic design feature...

Mark

 

[missinglinq]

I think what you really need to come to grips with is the fact that you're no longer using Paradox! If Paradox does the job for you, stick with it! Otherwise, take the time to learn Access and stop whining because Access isn't Paradox! A trumpet player, deciding to learn the piano, doesn't complain because the trumpet only takes three fingers to play, and the piano takes ten fingers!

 

[Chromedome]

Point taken Missinglinq
Don't get me wrong, I am impressed with what I have seen in Access so far, I simply thought there would be a quick way of pulling the desired fields into the record; especially as you can show the additional fields when when you activate the Combo Any suggestions?

But hey! Let's get stuck in and see what comes out!

Regards

CD

 

[neileg]

Missinglinq, it's more like a piano player learning the trumpet and finding they can't play chords!

 

[Chromedome]

Hey Neileg
I thought Andycap came from Manchester

CD

 

[missinglinq]

Missinglinq, it's more like a piano player learning the trumpet and finding they can't play chords!

Sure they can! They just have to play them one note at a time!

 

[missinglinq]

Here's a short tutorial I keep on hand to help newbies to Access understand the process.

Set up your combobox using the wizard and include the fields you need, i.e. Company, Address, City, State, etc. If the fields are added to the combobox in that order, then the syntax to use after a selection was made from the combobox would be:

Private Sub YourComboBox_AfterUpdate()
  Me.Company= Me.YourComboBox.Column(0)
  Me.Address = Me.YourComboBox.Column(1)
  Me.City = Me.YourComboBox.Column(2)
  Me.State = Me.YourComboBox.Column(3)
End Sub

Notice that the column index is zero based.

I suspect what you were referring to before

"especially as you can show the additional fields when when you activate the Combo box"

was using the combobox wizard to retrieve a record, and yes, it'll automatically fill in all the fields in this scenario.

We're all here to help you, CD! Moving into a new language is seldom easy! Post back as you need to, and hopefully, ye shall find the light!

Linq

 

[neileg]

Hey Neileg
I thought Andycap came from Manchester

CD

I'm shocked that you could think that!

Actually he's from Hartlepool, the home town of his creator, Reg Smyth, but we like to regard him as a generic northeasterner.

 

[Chromedome]

Thanks for your assistance Linq, much appreciated...

Neil: I remember reading years ago about Andycap being created, and when I think deeper, I believe it was when Reg Smyth was actually driving around Manchester when he came up with the idea of the character... not that I was ever any good at pub quiz's

Mark