This Database continues to give me a headache

[USAussie]

Hi,
Me again.
Stuck on yet another part of my database. I had it all up and running and this is an update request made by one of my other staff members.

I'm trying to get some auto fills going to make things faster for the sales team. However they are auto fills that need to save so that the data shows up on reports.

I followed some instructions for setting up a query then using a combo box to get auto fill fields, but I want the text boxes with
=combo281.column(1) to save the data to a field so say this formula will bring up the year. I want that year to be saved in the Year field.

So I read about trying use the after update function to enter this code:
Me.TextboxName = Me.ComboName.Column(2)

But I can't figure out how to get that to work. Having a major headache this is way beyond my skill level.
Can anyone help?

 

[ypma]

If your combo box's control source was me.textname The field showing in the dropdown would be save with that record . Is this what you are trying to achieve ?

 

[USAussie]

Not sure what you mean but what I want is for the salesperson to select the VIN of a camper being purchased from the combo box and that will then auto populate the Year, Make, Model, Type and Color fields.
I want the auto-populated data to be saved in my table so that it can be used for reports.

I was experimenting with the me.textname formula but can't figure out how to use it properly.

 

[ypma]

OK I Think i now understand . I agree with your code and I have tried to reproduce your desired outcome and your Me.textname = me.comboboxname.column(2) would give you the column 3 of your drop down list.
ie Make assuming the first column was the ID .
For the other, types, model and year etc . repeat the Me.txtname = me.comboboxname.column(3) and so on
I am using 2003 so hope this works for you

 

[USAussie]

um thanks but I mean understand the code. My problem is I can't get the code to work. I'm not sure I'm placing it into the SQL box properly.

 

[ypma]

Right Click on the combo box in design mode
Click the Properties Then Click .Events , Click After update .Use drop down to show Event Procedure click on the line and a box with 3 dots should appear at the end . click this and the sub you require will appear as below.

Private Sub ComboBoxName_AfterUpdate()

End Sub


Place the following inside the sub
Me.TextYear = Me.ComboBoxName.Column(1)
Me.TextMake = Me.ComboBoxName.Column(2)
Me.TextType = Me.ComboBoxName.Column(3)
Me.TextColor = Me.ComboBoxName.Column(4)

These Text boxes or whatever you call the field names must be bound to the table
I hope I have fully understood your post

 

[Simon_MT]

Why can't link the booking with the Camper table and just store the Camper because and just display the Year Make Type from this table.

Simon

 

[USAussie]

Thanks I'll try that code today and see if it works. As for just linking the two tables I need to generate invoices and quotes using this information, as well as final purchasing paperwork if they decide to buy the camper so I really need to store the information on what camper a person purchases with the rest of their data.

 

[USAussie]

Ok I tried to enter the code and it's coming up with a message saying error "method or data member not found" and it highlights this line:

Private Sub Combo281_AfterUpdate()

Any thoughts?

 

[RainLover]

You don't need to save all those Columns.

Just save the PK. From there you can call the PK and at the same time get the other fields.

This is part of basic Normalisation.

 

[USAussie]

What's the PK?

 

[RainLover]

A Table should be designed like this.

tblVehicles

VehiclePK as AutoNumber. Primary Key. Unique Identifer.
Year as Text
Make as Text
Model as Text
Type as Text
Color as Text

So if you store the Primary Key in another Table as the FK Foreign Key (e.g. a Number like 1001)

Then from the Foreign Key we can match the Primary Key and also get, Year, Make, Model, Type and Color.

The idea is to store the information ONCE, in one table. This is one of the first principals of Database Design.

 

[USAussie]

Lol ok rain thanks for the lesson. I appreciate the help but I'm just doing this to help my boss and I'm doing the best I can. No need for the lecture just because I'm not a pro at it or whatever. I will stick with how it is, and try and get this all to work using the primary key. It's just going to take more time because it's a difficult process for me since I'm not that advanced with SQL and the like.
But I'll find a way to make it work.

 

[RainLover]

Sorry

I did not mean to lecture. I already realised that you were not a pro. I was simply doing for you what so many before me have done for me.

That is they taught how to do things right for my boss.

Good luck with the project. I'll get out ot the way and let others help.

 

[USAussie]

Ok if I keep the combo box lookups then I now have to figure out how to get the same information to display in my reports.
I thought I could probably do a Dlookup but I keep coming up with an error. This is the code I'm using:

=DLookUp("[Year]","tblInventoryDatabase1","[VINNumber]=Reports!rptWriteUpSheet![VINofUnitofInterest]")

So I'm trying to get the report to look up the field "Year" based on the VIN number used in my report so it matches the info related to the VIN in the camper inventory table.

This is all very frustrating so any help is appreciated!

 

[ypma]

A lot of thought since i last posted on your post. I agree duplicating data is not the best practice .
How about this as another option . I am assuming you have two tables one with camper spec. and a Customer table of customers who will have a particular camper allocated
Your combo is i assume on the customer form This needs tobe bound to the a new field in the customer table "Vim " So the lookup table only has to lookup VINS and on click of the selected vin it will be saved to the Vin field in the for that customer .

The next step i would create a query adding both tables and join the two vims
You can then can drag in fields from both tables you require for your report.The Join properties will only include records where the joined fields from both tables are equal . Then create your Report using this new query as the data source .
I suspect this could be acheive another way but i have tried this and it works on my Pc ,Let me know if I am not making sense.

If you go down this road. consider removing the combo box completely and replace it with a text box and have the vin manually inputted
Also, depending on the frequency of your reports , a filter to exclude records that are not require . If there is a date of transaction on the customer forms that could be use in the criteria of the query.

 

[USAussie]

Thanks very much for your reply YPMA I will have a look into this tomorrow or next week when I have a chance and I'll get back to you. It sounds like it might work to me.

 

[RainLover]

Do you have your Relationships Set Up.

If so can you post a Pic.

If you are complete lost I will create a very simple Database and post it here.

It is weekend time now but I will get around to it. Unless someone beats me to it.

PS There should be no need for DLookup. Just join the tables.

 

[RainLover]

Have a look at the attached Demo Database.

It should demostrate what I am talking about.

 

[ypma]

USAussie, did you get round to testing the various options you were given , If you solve it i would like to know how, as i found it and interesting question .
Bob