Probably Something Stupid ........

[snagrat]

Please look at the attachment and see if you can understand the problem I am having.

open frmNewInvoice and in the top right combo select a Post Code. Then double click on a customer in the list box. This should then update the text boxes to the left of this list box depending on which customer is selected. However it doesn't and I haven't got a clue why.

It will be probably just be a stupid error I have made somewhere.

And sorry to you Pros if it is in a bit of a mess but I am a bit of a Newbie to Access



And also if you fancy having a look, I can't understand why Query2 doesn't work either.

Thanks

 

[WayneRyan]

snagrat,

I don't like those macros ...

On the DoubleClick event:

Me.txtSurName = Me.lstDetails.Column(1)

And Me.txtCustomerID = DLookup("[CustomerID]", _
"tblCustomerDetails", _
"[SurName] = '" & Me.lstDetails.Column(0) & "' And " & _
"[FirstName] = '" & Me.lstDetails.Column(1) & "'")

hth,
Wayne

 

[snagrat]

Well I will use that code as I can't get it to work.

But I use macro's because I hate VBA. But thanks for sorting it out.

Cheers

 

[snagrat]

I have pasted your code and I get the error:

"Cant assign a value to this object"

Whats wrong there? I have this:

Private Sub lstDetails_DblClick(Cancel As Integer)
Me.txtSurname = Me.lstDetails.Column(1)

Me.txtCustomerID = DLookup("[CustomerID]", _
"tblCustomerDetails", _
"[SurName] = '" & Me.lstDetails.Column(0) & "' And " & _
"[FirstName] = '" & Me.lstDetails.Column(1) & "'")
End Sub

 

[WayneRyan]

snagrat,

Let me try it, I'll get back to you.

Wayne

 

[WayneRyan]

snagrat,

Oh oh ...

The CustomerID is an autonumber field bound to your table
and thus you can't enter a value into it. Its only purpose is
to give a unique ID to a customer. It doesn't have to even
be seen by people.

Initially I only looked at your list box. You have a severe
problem with your application:

Your Main form is bound to your tblCustomerDetails. Your
shopping cart is unbound, It can never store anything.

I see that you have several other tables. I have to go to a
lab now, but basically your form should have:

A main form for invoices:
Invoice #, Date, Salesperson, etc ...
AND a CustomerID chosen from a combo based on
tblCustomerDetail. If this is a multi-column combo, you can
display (but not bind) related customer info.

It should have a subform (kind of like your shopping cart).
It should have the Invoice #, quantity,
AND Product # chosen from a combo based on tblProducts.
If this is a multi-column combo, you can
display (but not bind) related Product info.

Your tables seem OK. I think that with a little work, your
form can reflect what your table design intends.

let me know ...

hth,
Wayne

 

[snagrat]

I respect your views on my database and have taken it all into account.

This is mainly for a piece of college coursework which is due in shortly so don't really have time to re design it like you have said.

Today I have create it so it is working how I want it to work, even though it might not be the best way in an experts view (like yourself )

I have decided to change the CustomerID from AutoNumber to Text and have the user specify the CustomerID (depending on nanme, DOB etc...) - Therefore allowing me to use the method I am currently using.

Thank you for taking a look at it, and for your advise. Something to put in the conclusion part I spose.

Thanks once again