Re: Edit multiple tables with one form per record

ricky.agrawal@xxxxxxxxx wrote:

Salad, thanks for your advice! I did move to having all the stores in
one table. It is truly much simpler.

Now that you bring up the issue of clones, is there a check I can
install that will prevent a duplicate record from existing using the
following criteria?

if((SalesDate && StoreID) exist)
     DisplayErrorBox;

Or something like that to prevent someone from incorrectly entering in
data for a store.

I, for the most part, ALWAYS have an autonumber field as my primary key. It makes life less complicated. For example, in the BeforeUpdate event I might want to check if something exists. Ex:
If Me.NewRecord then
Dim var As Variant
var = Dlookup("ID","TableName","StoreID = " & _
Me.StoreID & " And ID <> " & Me.ID)
If Not IsNull(var) Then
msgbox "This store record already exists"
Cancel = True
Endif
Endif

Now, the above is a bit unnecessary but sometimes you want to check for a record that contains the same info but the AUTONUMBER does not match the current autonumber field.

Your situation is a classic example for a form/subform. In this instance I might have an unbound form...it really depends on how you set up your tables. In the main form, I might have a combo box that lists all of your stores with 2 columns; StoreID and StoreName. In a textbox I'd have the date field. In the AfterUpdate event for the combo box, and date text box, and perhaps the OnCurrent or OnLoad events I'd have some code like
SetFilter

Next, I'd have a subform that would list the data for the store/date selected in the main form. It would have no filter.

Now drop the subform into the main form.

Let's say the main form is called MainForm and the subform is called SubForm. Your SetFilter code may look something like this...
Private Sub SetFilter
Forms!MainForm!SubForm.Form.Filter = _
"StoreID = " & Me.ComboStoreID & " And " & _
"StoreDate = #" & Me.InputDateField & "#"
Forms!MainForm!SubForm.Form.FilterOn = True

You can shorten the references like this
	Me("SubForm").Form.FilterOn = True

Of course, if the main table was bound to a table that stored the StoreID and Date and the subform contained date related to it, the form/Subform could be linked with the Master/Child relationship.


.

Relevant Pages

  • RE: Attention Ken Sheridan
    ... store and the loacation of that store, which is a drop downlist ... damage, how much he needs to repay(as we pay off all the claims then deduct ... I am not sure what you mean by the city and state and having a seperate city ... works subform within a claims form, ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Attention Ken Sheridan
    ... store and the loacation of that store, which is a drop downlist ... damage, how much he needs to repay(as we pay off all the claims then deduct ... I am not sure what you mean by the city and state and having a seperate city ... works subform within a claims form, ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Attention Ken Sheridan
    ... store and the loacation of that store, which is a drop downlist ... damage, how much he needs to repay(as we pay off all the claims then deduct ... I am not sure what you mean by the city and state and having a seperate city ... works subform within a claims form, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: One-to-one primary data synchronization
    ... I have set up a main form with an embedded subform based on this relationship, but I'd really like to have a linked form, while still enjoying the autonumber/number sychronization described below. ... > When data is added to the main form, not only does the Subscriber autonumber field populate automatically in the main form, but the SAME number automatically populates in the CoSubscriberID field as a result of the subform linkage and the fact that SubscriberID is driving CoSubscriberID. ...
    (microsoft.public.access.formscoding)
  • Re: subform combobox filter problem
    ... > I have created a bound subform on an unbound masterform linked ... > an unbound combobox on the masterform and an invisible field on the ... what you see when you select a store is a list ... > subform's query (simple query on a single table sorting items ...
    (comp.databases.ms-access)