By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

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

hyperpau
Expert 100+
P: 184
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?
Jun 12 '07 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
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?
Jun 13 '07 #2

hyperpau
Expert 100+
P: 184
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
Jun 14 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Jun 14 '07 #4

hyperpau
Expert 100+
P: 184
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? :)
Jun 14 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_BeforeInsert(Cancel As Integer)
  3.     With Me
  4.         .RecordNo = DMax(.RecordNo.ControlSource, .RecordSource) + 1
  5.     End With
  6. End Sub
  7.  
  8.  
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?
Jun 14 '07 #6

FishVal
Expert 2.5K+
P: 2,653
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
Jun 15 '07 #7

hyperpau
Expert 100+
P: 184
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.
Jun 15 '07 #8

hyperpau
Expert 100+
P: 184
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?
Jun 20 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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
Jun 21 '07 #10

hyperpau
Expert 100+
P: 184
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCancel_Click( )
  2.  
  3. Me.Undo
  4. DoCmd.Close, "frmTransfer"
  5.  
  6. End Sub
It works undoing all the data entered in the frmTransfer Main Form
but the subfrmTransfer data is kept and stored.
Jun 25 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.