Newbie needs help with combo box and editting record (1 Viewer)

[Funkyaccess]

Hi,

As I'm new to this forum I will briefly describe my experience, I have extensive Excel and Excel VBA knowledge and have been working with ORACLE products for the past 3 years.

I need some help with a combo box (It maybe a design fault from my part) but here goes

I got two tables

Tbl_stores
Store_id(autonumber)
store_name (text)
in_use(yes/no)

tbl_sales
sale_id(autonumber)
store_id (number)

The two tables are linked in a one to many relationship with the tbl_sales store_id setup as a lookup

On the form I have a combox which is populated by a query i have that only shows active stores. This works great when adding records but if you editting and you edit a store that was closed after it was add the combobox simply defaults to the first availble value rather than have the old store plus the current active stores. Where should I start?

Also I want to be able to "merge" stores together so would it be better to do the "merge" in my reports and only merge when a date after the merge?

Many Thanks for your help.

 

[CameronM]

On the form I have a combox which is populated by a query i have that only shows active stores.

This is your problem, the query does no include the inactive stores. You could change the Row Source of the combobox whenever an existing record is opened. You would add code to the form's On Current event to test that the form is displaying an existing record, perhaps by looking at the value of the primary key

If txtSales_id > 0 then
     cboStoreId.RowSource = "NameOfQueryWithAllStores"
else
      cboStoreId.RowSource = "NameOfQueryWithActiveStores"
End if

Also I want to be able to "merge" stores together

Are you wanting to merge all the sales data for one store into another?

 

[Funkyaccess]

Thanks Cameron,

What you said makes perfect sense. Is there maybe a option like somehow setting a condition to the query. I.e if I had a query say

select * from tbl_stores

Is there a way I could some how pass a condition (i.e where clause) to the query to restricted it.

If txtSales_id > 0 then
      'return the query with all stores    
else
      ' Set a condition to only display active stores
      ' return the stores
End if

Are you wanting to merge all the sales data for one store into another?

Thats right. When I develop my report I have to have a Carry forward figure of the sales and I was wanting to group the sales for stores that merged

eg.

Theres store A, B and C. C merges with A on the 24/09/09 so on 25/09/09 Store's A report should combine A + C figures.

I was thinking perhaps I need another table that stores the stores and how they would be grouped and the date it becomes effected.

tbl_merged_stores
store_from NUMBER
store_to NUMBER
date_merged DATE/TIME

eg data
C
A
24/09/09

 

[DCrake]

To change the rowsource for your combo box you can do it by setting the rowsource as an SQL statement opposed to a query, such as

If Active Then
   Me.Combo.Rowsource = "Select ... From ..."
Else
   Me.Combo.Rowsource = "Select ... From ... Where ..."
End If

David

 

[CameronM]

Theres store A, B and C. C merges with A on the 24/09/09 so on 25/09/09 Store's A report should combine A + C figures

Could you not create a new merged store so that A and C become historical and a new merged store 'D' becomes current as of 25/09/09. All sales transactions for the merged stores are allocated to D. A and C would show no sales after the date of the merge. Just a thought.

If that doesn't work, then I guess you would have to go with a seperate table that links merged stores, but that may get hard, especially if the date range being reported spans across the date the stores merge.