Question Help required...

[WildmanUK]

Hello,

I am trying to create database and having a bit of a problem getting my head around it, its been a long time since I did anything in access.

I will outline the tables and what I want to display and hopefully someone can come up with a easy solution

Tables: Fields

Customer: ID, customerCode, customerName

Location: ID, locationCode, customerCode, address1, address2, address3, postCode.

Contacts: ID, customerCode, locationCode, contactName, email, telephone, fax.

Basically I have a form that shows the customerCode and customerName. I then want a combo box showing the locationCodes from Location table but only the ones that match the current records customerCode.

Then when I select a location from the drop down, I want it to list the company address and then a table of all the contacts linked to that location.

If I set up a 1 to many relationship from Customers to Locations linked by customerCode, I can make a subform showing all the locations for that company, but I want to only show the one that matches the combobox selection.

Then I need another subform that will show only the contacts for the selected location.

I had tried a few approaches but keep running into problems, so thought I would ask on here and see if theres a simple solution?

I had tried to create a query based on the current record and the value of the combo box then populate the location address subform from that, but not sure if this would update the table if a value was changed or would it just show the values in the fields, but not update if changed.

Hopefully someone will have an idea of how to sort this?

If you need any more information that I might have missed please ask..

Thanks in advance.

WildmanUK

 

[CJ_London]

A number of points in no particular order

first - this is good - no spaces or non alpha numeric characters in field names

1. rather than using ID as the unique key name use something more meaningful like CustomerID (although personally I prefer CustomerPK)
2. in your locations table, use ID/CustomerID (or my preference CustomerFK) rather than customercode
3.same for contacts table/locationcode

what you are looking for is a flavour of something called 'cascading combos' - google this and you will find many examples

 

[WildmanUK]

Hi,

Thanks for the quick reply.

I have managed to get it mostly working but I have a more cosmetic problem now rather than a logic problem.

I have produced a query which selects the locationID from the Location table based on the customerCode of the current record being displayed in the main form.

I have then created the combo box and used the query as the source for the values. When I load up the form it and open the combo box it shows the values but it has a blank field for the default value, followed by the correct values in the rest of the drop down from the combo box. If I then switch record to a new customer where there was a blank for the default view it now shows the value that was previously selected before I changed the current record.

Is there a way to make it so the combo box will start with the first value in the box and not a blank followed by the correct list?

Thanks.

Regards,

WildmanUK

 

[CJ_London]

you could try in the form current event putting some code like

me.combo=me.combo.columns(me.combo.boundcolumn,0)

 

[JLCantara]

WildManUK: can you attach a screenshot of your form? Is your combo in a subform? If it is 'linked' to main form, why don't you initialize it when you select a record?

 

[WildmanUK]

Hi,

The Customer Code, Customer Name section and the combo box are part of the main form. Then there's a subform for the address part and another subform for the contacts section.

I have a query that selects all records from locations where the customerCode is the same as the one currently in the textBox on screen, then when I created the combo I selected the query as the source and selected the locationCode field to display.

Then I have another query that selects all in the Locations table where Locations.customerCode is equal to the textBox on the main form and then Locations.locationCode is equal to the value in the combo. This is used to fill the address subform.

Thanks.

Regards,

WildmanUK

 

[WildmanUK]

I have attached a second preview so you can see the problem more clearly.

When the form loads up the combo has no default value, when I open the combo up the other options are listed below the blank space.

This isn't that much of a problem when you first load the form but it gets messy when you change record.

Say customer 1 has an office with the code MANC but customer 2 hasn't.

I select MANC on customer one then move to customer two, As there is no default value, MANC is still shown at the top until I select one of customer 2's listed locations.

What I want to do is have it so it sets the combo to the first item in the list as it loads up and then also fill the address etc in based on that default value. At the moment everything other than customerCode and customerName is blank until I select one of the Locations from the combobox.

EDIT: I have tried the following Code in the Form current event

Dim message As String
message = "Location"
Me.ComboLocation.DefaultValue = "'" & message & "'"

This works and sets the default value to the string "Location", when I try and set it to the first item in the combo box it returns an error. I'm wondering if the problem is due to the combo box being populated using a parameter query based on the current record.

Could it be that during the current event the form has loaded but the comboBox has no values yet? So its returning an error?

The code I tried was as follows

Dim message As String
message = Me.ComboLocation.Column(1, 0)
'Me.ComboLocation.DefaultValue = "'" & message & "'"

This comes up with invalid use of Null which makes me think there are no current items in the combo box when it tries to pull the value out.

Also tried the same with the 2nd line as

message = Me.ComboLocation.Column(1, 0).Value

And

message = Me.ComboLocation.ItemData(0).Value

These both come back saying no object.

Thanks.

Regards,

WildmanUK

 

[WildmanUK]

Right I seem to have fixed it thanks for replies.

I ended up putting the following code in the Current event of the Form

Me.ComboLocation = Me.ComboLocation.ItemData(0)
Me.Refresh

The default value is set when the form is loaded and when the record changes and the subforms update to show the default selections address and contacts

Thanks again!

WildmanUK

 

[CJ_London]

this works for me in form current event

ComboLocation.DefaultValue = ComboLocation.Column(ComboLocation.BoundColumn - 1, 0)