Forms - with Subforms based on a parameter Query, for whic Results can be edited.

[hyperpau]

I've been going nuts about it and I've been searching all over the forums to find answers. Can somebody please help me?


I am a somehow advanced Access user with basic VBA knowledge.
(No ADO or SQL)

I have created this database for a Repair Center company to keep track of items that are received, transferred to a third party technicians, and released back to customers.

I created 3 Main tables with the following fields.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)

2) tblTransferred
---TransferNo : Autonumber
---TransferDate : Date
---TransferItemsNo : ??? - Help Here
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)

3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : ??? - Help Here
---Status : Combobox (need to specify source)


Then I have 2 joining tables to connect the many-to-many relationships:

1) tblTransferItems
---TransferItemsNo : Autonumber
---TransferNo : Links to tblTransfer
---ReceiveNo : Links to tblReceive

2) tblReleaseItems
---ReleaseItemsNo : Autonumber
---ReleaseNo : Links to tbl Release
---TransferNo : Links to tblTransfer


And here are my forms where I am having problems with.

1) frmReceive : source is qryReceive

2) frmTransfer : Source is tblTransfer
-----subfrmTransferItems : Source is tblTransferItesm

3) frmRelease : Source is tblRelease
-----subfrmReleaseItems : Source is tblReleaseItems


4) frmSetTransfer ': unbound

5 frmSetRelease ) : unbound


What I want to happen is that user inputs information on tblTransfer which are
multiple items from the tblReceive.
And also input information on the tblRelease which are multiple items from the
tblTransfer:

Here is the Big Picture.

ReleaseNo1
-----TransferNo1
----------ReceiveNo1
----------ReceiveNo2
-----TransferNo2
-----------ReceiveNo3
-----------ReceiveNo4
ReleaseNo2
-----TransferNo3
------------ReceiveNo5
------------ReceiveNo6
-----TransferNo4
-----------ReceiveNo7
-----------ReceiveNo8


What I want is by using the unbound frmSetTransfer, items would appear
in the subfrmTransferItems.
So I figure maybe the source for the subform
would be a parameter query based on the unbound form.

But I want it in a way that after the results are shown, users can see all
information (not only the first column) and edit them as well.
When a user selects a choice in the combobox Technician of frmTransfer,
all the Technician Field of subfrmTransferItems will change
according to that as well.
The same goes to the Status Field of frmTransfer which should update
the Status field of all records on the subfrmTransferDetails.

I can't seem to find a way! Is my plan too complex?

Is it even possible to have tables and forms related in the way that I was planning it in the Big Picture above?

[FishVal]

I've been going nuts about it and I've been searching all over the forums to find answers. Can somebody please help me?


I am a somehow advanced Access user with basic VBA knowledge.
(No ADO or SQL)

I have created this database for a Repair Center company to keep track of items that are received, transferred to a third party technicians, and released back to customers.

I created 3 Main tables with the following fields.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)

2) tblTransferred
---TransferNo : Autonumber
---TransferDate : Date
---TransferItemsNo : ??? - Help Here
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)

3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : ??? - Help Here
---Status : Combobox (need to specify source)


Then I have 2 joining tables to connect the many-to-many relationships:

1) tblTransferItems
---TransferItemsNo : Autonumber
---TransferNo : Links to tblTransfer
---ReceiveNo : Links to tblReceive

2) tblReleaseItems
---ReleaseItemsNo : Autonumber
---ReleaseNo : Links to tbl Release
---TransferNo : Links to tblTransfer


And here are my forms where I am having problems with.

1) frmReceive : source is qryReceive

2) frmTransfer : Source is tblTransfer
-----subfrmTransferItems : Source is tblTransferItesm

3) frmRelease : Source is tblRelease
-----subfrmReleaseItems : Source is tblReleaseItems


4) frmSetTransfer ': unbound

5 frmSetRelease ) : unbound


What I want to happen is that user inputs information on tblTransfer which are
multiple items from the tblReceive.
And also input information on the tblRelease which are multiple items from the
tblTransfer:

Here is the Big Picture.

ReleaseNo1
-----TransferNo1
----------ReceiveNo1
----------ReceiveNo2
-----TransferNo2
-----------ReceiveNo3
-----------ReceiveNo4
ReleaseNo2
-----TransferNo3
------------ReceiveNo5
------------ReceiveNo6
-----TransferNo4
-----------ReceiveNo7
-----------ReceiveNo8


What I want is by using the unbound frmSetTransfer, items would appear
in the subfrmTransferItems.
So I figure maybe the source for the subform
would be a parameter query based on the unbound form.

But I want it in a way that after the results are shown, users can see all
information (not only the first column) and edit them as well.
When a user selects a choice in the combobox Technician of frmTransfer,
all the Technician Field of subfrmTransferItems will change
according to that as well.
The same goes to the Status Field of frmTransfer which should update
the Status field of all records on the subfrmTransferDetails.

I can't seem to find a way! Is my plan too complex?

Is it even possible to have tables and forms related in the way that I was planning it in the Big Picture above?

Hi!

As I've got it the things are going this way.

1. tblReceived.ReceiveNo uniquely identifies single item
2. tblTransferred.TransferNo uniquely identifies single transfer of multiple items
3. tblReleased.ReleaseNo uniquely identifies single release of multiple items
4. Not all items of single transfer must be within single release.

If so the only thing you need is to add FK fields based on PK fields in tables tblTransferred and tblReleased to tblReceived.

1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
---TransferNo : Long, FK, lookup(tblTransferred)
---ReleaseNo : Long, FK, lookup(tblReleased)

2) tblTransferred
---TransferNo : Autonumber, PK
---TransferDate : Date
---TransferItemsNo : Remove this
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)

3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : Remove this
---Status : Combobox (need to specify source)

The rest tables are not needed, use joins.
Does this look like what you've been expected?

[hyperpau]

Hi!

As I've got it the things are going this way.

1. tblReceived.ReceiveNo uniquely identifies single item
2. tblTransferred.TransferNo uniquely identifies single transfer of multiple items
3. tblReleased.ReleaseNo uniquely identifies single release of multiple items
4. Not all items of single transfer must be within single release.

If so the only thing you need is to add FK fields based on PK fields in tables tblTransferred and tblReleased to tblReceived.

1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)
---TransferNo : Long, FK, lookup(tblTransferred)
---ReleaseNo : Long, FK, lookup(tblReleased)

2) tblTransferred
---TransferNo : Autonumber, PK
---TransferDate : Date
---TransferItemsNo : Remove this
---Technician : Combobox (no need to specify source)
---Status : Combobox (no need to specify source)

3) tblReleased
---ReleaseNo : Autonumber
---ReleaseDate : Date
---ReleaseItemsNo : Remove this
---Status : Combobox (need to specify source)

The rest tables are not needed, use joins.
Does this look like what you've been expected?

Yes. Absolutely!!! I never though somebody would understand me
coz i had a hard time explaining my problem as well. Thanks.
You gave me more light to this.

just one more thing.
I already have an Autonumber field ( just didn't specify)
aside from the ReceiveNo, TransferNo, and ReleaseNo.
I've set this fields to Number (Long integer) and I want it
to increment everytime the form loads in add entry mode.

But it just doesn't increment at all.
The Format's for these fields are
ReceiveNo : "HPRec 07"00
TransferNo : "HPTra 07"00
Release No: "HPRel 07"00

and I have my forms coded like this:

Private Sub Form_Load( )

If Me.NewRecord Then
Me![ReceiveNo] = DMax([ReceiveNo], "tblReceive") + 1
Else
Me![ReceiveNo] = Me![ReceiveNo]
End If

End Sub

But it just doesn't increment. every time the form is opened
at add entry mode, the fields just show as HPRec 0700, HPTra 0700, and HPRel 0700. It doesn't increment it. Is there something I've missed here?

Thanks in advance

[FishVal]

Yes. Absolutely!!! I never though somebody would understand me
coz i had a hard time explaining my problem as well. Thanks.
You gave me more light to this.

just one more thing.
I already have an Autonumber field ( just didn't specify)
aside from the ReceiveNo, TransferNo, and ReleaseNo.
I've set this fields to Number (Long integer) and I want it
to increment everytime the form loads in add entry mode.

But it just doesn't increment at all.
The Format's for these fields are
ReceiveNo : "HPRec 07"00
TransferNo : "HPTra 07"00
Release No: "HPRel 07"00

and I have my forms coded like this:

Private Sub Form_Load( )

If Me.NewRecord Then
Me![ReceiveNo] = DMax([ReceiveNo], "tblReceive") + 1
Else
Me![ReceiveNo] = Me![ReceiveNo]
End If

End Sub

But it just doesn't increment. every time the form is opened
at add entry mode, the fields just show as HPRec 0700, HPTra 0700, and HPRel 0700. It doesn't increment it. Is there something I've missed here?

Thanks in advance

You don't have and moreover not able to write values to autonumber field.
Every time a new record is added Access will fill autonumber field with next number. You Me![ReceiveNo] is probably not linked to tblReceived.ReceiveNo field, otherwise it will result in error (smthng like "field is read-only").

If you want ID's to appear with prefix via format property then you should do the following:

In form ;) design view.

Check that control format property is set to "HP... 07"00
And check that the control is linked to the source table autonumber field!

And get rid of the code above. It will never work with Autonumber fields.

Good luck.

[hyperpau]

You don't have and moreover not able to write values to autonumber field.
Every time a new record is added Access will fill autonumber field with next number. You Me![ReceiveNo] is probably not linked to tblReceived.ReceiveNo field, otherwise it will result in error (smthng like "field is read-only").

If you want ID's to appear with prefix via format property then you should do the following:

In form ;) design view.

Check that control format property is set to "HP... 07"00
And check that the control is linked to the source table autonumber field!

And get rid of the code above. It will never work with Autonumber fields.

Good luck.

Thank you for your continuous support.

Again, the ReceiveNo, TransferNo, and ReleaseNo are
not autonumber fields. I have another field for autonumber.
These are just number fields set to long integer.

ex:
tblReceive
-----ReceiveID <----autonumber; PK
-----ReceiveNo
-----Description
-----SerialNo
-----Status
-----OSP

I also tried what you suggested. I got rid of the other tables
and just added the lookup fields in the tblReceive.
It worked out so damn good. exept that, it only allows
me to input or receive new items in the frmTransfer.
What I want is that on the frmTransfer which is bound to tblTransfer,
I can just select items by a combo box bound to tblReceive.

With the scenario you taught me, I can only input new items, meaning
I will be receiving items using the Transfer form.

What I want is I receive Items using the frmReceive.

Then when I am about to transfer these items to outside technicians,
I would record the transfer using the frmTransfer and then selecting
the items to transfre instead of typing them all over again.

But the problem is, even if I am able to succesfully create the combobox
to select items to transfer, it just shows me one field.
I want to be able to see all fields of the item and edit them on the same
frmTransfer.

Did I say it clear enough? :)

[FishVal]

Thank you for your continuous support.

Again, the ReceiveNo, TransferNo, and ReleaseNo are
not autonumber fields. I have another field for autonumber.
These are just number fields set to long integer.

ex:
tblReceive
-----ReceiveID <----autonumber; PK
-----ReceiveNo
-----Description
-----SerialNo
-----Status
-----OSP

I also tried what you suggested. I got rid of the other tables
and just added the lookup fields in the tblReceive.
It worked out so damn good. exept that, it only allows
me to input or receive new items in the frmTransfer.
What I want is that on the frmTransfer which is bound to tblTransfer,
I can just select items by a combo box bound to tblReceive.

With the scenario you taught me, I can only input new items, meaning
I will be receiving items using the Transfer form.

What I want is I receive Items using the frmReceive.

Then when I am about to transfer these items to outside technicians,
I would record the transfer using the frmTransfer and then selecting
the items to transfre instead of typing them all over again.

But the problem is, even if I am able to succesfully create the combobox
to select items to transfer, it just shows me one field.
I want to be able to see all fields of the item and edit them on the same
frmTransfer.

Did I say it clear enough? :)

Once more Hi!

A previous version of tblReceive looked this way

I have created this database for a Repair Center company to keep track of items that are received, transferred to a third party technicians, and released back to customers.

I created 3 Main tables with the following fields.
1) tblReceived
---ReceiveNo : Autonumber
---ReceiveDate : Date
---Description : Combobox (no need to specify source)
---SerialNo : Text
---Status : Combobox (no need to specify source)
---Technician : Combobox (no need to specify source)

When did you release the new one? :)
I can only guess why do you need two Autonumber fields.
Well, in any case here is my suggestion.

 
Private Sub Form_BeforeInsert(Cancel As Integer)
    With Me
        .RecordNo = DMax(.RecordNo.ControlSource, .RecordSource) + 1
    End With
End Sub
 
 

What concerns table structure.

With enough coding you can build friendly interface with both 3-table and 5-table schemas.


for ex. for transfers

With 3-table schema:

Transfer details and Listbox-to-Listbox interface on the main frmTransfer form and subformTransferredItems subform (w/o additions allowed) with item details.

With 5-table schema:

Transfer details on the main frmTransfer form and subformTransferredItems subform (additions allowed) with item details.
Subform source is

SELECT tblReceived.*, tblTransferredItems.* FROM tblReceived INNER JOIN tblTransferredItems ON tblReceived.ReceivedID = tblTransferredItems.ReceivedID;

For the sake of suitability you can make multicolumn width combo associated with tblTransferredItems.ReceivedID.
For the sake of suitability you should modify this combo RowSource property programmatically to prevent user from multiple addition of the same item and addition an item been released.
Additionally you should prevent multiple addition of the same item using table indices.

Helps?

[FishVal]

With 3-table schema:

Transfer details and Listbox-to-Listbox interface on the main frmTransfer form and subformTransferredItems subform (w/o additions allowed) with item details.

With 5-table schema:

Transfer details on the main frmTransfer form and subformTransferredItems subform (additions allowed) with item details.
Subform source is

3-table schema
frmReceived - item additions allowed
frmTransferred/subform - item additions allowed
frmReleased/subform - item addition not allowed

5-table schema
frmReceived - item additions allowed
frmTransferred/subform - item additions allowed
frmReleased/subform - item addition allowed

[hyperpau]

3-table schema
frmReceived - item additions allowed
frmTransferred/subform - item additions allowed
frmReleased/subform - item addition not allowed

5-table schema
frmReceived - item additions allowed
frmTransferred/subform - item additions allowed
frmReleased/subform - item addition allowed

Thanks, I'll try this as soon as i get back to work, Monday.

[hyperpau]

Thanks, I'll try this as soon as i get back to work, Monday.

You're the man FishVAl!!!!! your INNER JOIN statement actually worked so much!!!!

There's only one problem I have now.
I have a cancel button on the from with the subform for the Transfer.
But Everytime I click it, it does not undo the entries made in the subform. How do do it that it will undo the entry for both the form and subform?

[FishVal]

You're the man FishVAl!!!!! your INNER JOIN statement actually worked so much!!!!

There's only one problem I have now.
I have a cancel button on the from with the subform for the Transfer.
But Everytime I click it, it does not undo the entries made in the subform. How do do it that it will undo the entry for both the form and subform?

Hi!

Glad it was somewhat helpful.
What concerns Cancel button.

• I would like to see code executed with the button click
• I would like to know what relationship schema you've chosen at last, to avoid any misunderstanding plz provide full metadata

[hyperpau]

Hi!

Glad it was somewhat helpful.
What concerns Cancel button.

• I would like to see code executed with the button click
• I would like to know what relationship schema you've chosen at last, to avoid any misunderstanding plz provide full metadata

Well, I don't really know the schema.
What I did is changed the data source of the subform toa SELECT
statement using the INNER JOIN you taught me.

This is what I have done with the cmdCancel_Click of the frmTransfer:

Private Sub cmdCancel_Click( )
 
Me.Undo
DoCmd.Close, "frmTransfer"
 
End Sub

It works undoing all the data entered in the frmTransfer Main Form
but the subfrmTransfer data is kept and stored.