Looking up data from a table to set Form Field default

noobSPX

Registered User.
Local time
Today, 00:54
Joined
Feb 11, 2015
Messages
16
I have a basic invoicing setup, with a Form (Invoices) and subform (InvoiceDetails).

When in the subform, i have a combo box to choose a Product Code (saved in table as PCode).

I want now to auto fill in the NettPrice and (Product Description) PDesc fields in the subform row - by looking these up in the Products Table and entering the data into the relevant fields on the Subform. This lookup will be based on PCode.

I tried all sorts of methods and the one i favour, if i could get it to work, is setting up a Function then calling this function from the Default Value property of each field involved.

So, for the Product Description field (PDesc), i created a Function as follows:

Function GetDesc () As String
GetDesc = DLookup ("[PDesc]", "[Products]", "[PCode] = " & Forms!InvoiceDetails!PCode)
End Function

Then i try to call by entering =GetDesc () into the Default Value property for the PDesc field.

I seem to have a syntax problem with my function code......any ideas?

Please note, I am a complete novice, so assume i don't know much. Also I know some of my values like NettPrice need not be fields on my Invoice Details table, but the prices change and I also need to be able to overwrite prices etc when typing invoice (products table price is only a guide price).

Thanks in advance
 
Thanks for the reply and your patience.

Ok, taking the advice from the 2nd link, i should merely be selecting all the required fields (Id, PCode, PDesc, NettPrice, VatRate) as the Control Source of my PCode Combo.

Then I have the following code in AfterUpdate property of PCode combo field:

Private Sub Combo25_AfterUpdate ()

Me.PDesc=Me.Combo25.Column (3)
Me.NettPrice=Me.Combo25.Column (4)
Me.VatPrice=Me.Nettprice * Me.Combo25.Column (5) / 100

End Sub

I get my combo box as i want, allowing the PCode to be entered. The other fields stay blank, no matter where i navigate to.

I'm on a steep learning curve here but i think if i can grasp this, the remaining forms i need will be a dawdle lol
 
it all depends if the PDesc etc fields are bound or not

if they are bound then Pauls suggestion as interpreted by you is the way forward i.e.
Me.PDesc=Me.Combo25.Column (3)


if not then you don't need a function, you just enter the following into the control source of the unbound controls - so for the PDesc control you would have a control source of
=Me.Combo25.Column (3)
 
Make sure the column count property of the combo is correct. It should be the number of fields returned by the row source.
 
it all depends if the PDesc etc fields are bound or not

if they are bound then Pauls suggestion as interpreted by you is the way forward i.e.
Me.PDesc=Me.Combo25.Column (3)


if not then you don't need a function, you just enter the following into the control source of the unbound controls - so for the PDesc control you would have a control source of
=Me.Combo25.Column (3)

CJ, was that not addressed clearly enough in my link? I'd be happy to rewrite it if it isn't clear.
 
Hi and thanks again. I checked column count and it was set to 4 instead of 5.
Still nothing showing up in Fields.
I'm wondering if my syntax is ok or if i should add procedure to another place.
 
Is there really a space after Column? There shouldn't be:

Me.PDesc=Me.Combo25.Column(3)

If that's not it, can you post the db here?
 
Paul, Sorry - I was just responded to the OP's interpretation. Your link is clear:)
 
No, no spaces, it's just my typing sorry.

Tried a few times, not getting anything into the PDesc etc fields.

I've attached my database, it's the Invoices Form and Invoice Details subform that I'm stuck on.

Excuse my novice attempts at form design, i hadn't expected anyone well versed to be looking.
 

Attachments

The code appears to be working fine. Did you enable code to run, or put the database in a trusted location (is any code running)? I'd point out that the column property is zero based, so this:

Me.PDesc = Me.Combo25.Column(3)

refers to the 4th column in the row source. Since there are 5 and you use Column(5), I think you may be one off from what you want.
 
You sir are a genius!
I googled Trust Centre and enabled macros option...that seems to sort my problem.

Yep i notice what you mean re Columns, in the Combo box I'm bound to column 2 as i should, but in Code it starts count from 0. Sorted this too and now able to continue my pet project.

Thanks so much for your help my hair was getting pulled out past few days (Cannot promise I'll not be back lol).
 
Would a null value in one of the Me.... lines knock it off in any way.

In the main form I have a CCode combo picking a customer. on AfterUpdate I call a number of Column values, but it only lifts CName, CCompany and Address line 1....it should lift more address, town etc values.

The only thing i can see is that the Address Line 2 column is Null....would that affect my results?

I'm looking at Nz () should i be?
 
Nz() would certainly be a way around Null values, yes.
 
Tried Nz function but it's not null values that is causing the prob.

I call a number of columns and insert the value into form fields, but it only seems to insert the first few, leaving rest blank.

I'm using an AfterUpdate prodproedure with:

Me.IContact=Me.List51.Column(2)
Me.ICompany=Me.List51.Column(3)
Me.IAddress1=Me.List51.Column(4)
Me.IAddress2=Me.List51.Column(5)
Etc...

It seems to only work down to IAddress1 then leaves out the rest, or at least leaves the relevant fields blank.

The Subform is working a treat now, eben have all my totals and subtotals calculating the way i want, so a big Cheers for that.
 
See if this helps...again. It applies to the listbox too. :p

Make sure the column count property of the combo is correct. It should be the number of fields returned by the row source.
 
Yep, missed that one again.

Absolutely brilliant, my form is acting exactly as i want, subform working great, totals all automatically updating across form/subform and all default data filled in from other tables.

You have got me past a bit I've been seriously stuck at, and I've learned a lot.
Thanks again, I appreciate your help and patience.

What a great forum !!!
 
Excellent, glad we were able to help. Welcome to the site by the way!
 

Users who are viewing this thread

Back
Top Bottom