Continuous Forms (1 Viewer)

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
I have a form where I'm entering data relating to each inbound shipment such as date of arrival, vessel name, etc...however i'd like also to enter invoice numbers and values that cover each shipment. one shipment can have more than one invoice. this means that for one shipment i need to enter , for example, 10 invoice numbers and 10 values.

I thought abt having two tables...one table having the shipment data and another table that keeps the list of invoice numbers/values of the shipments (having a foreign key relating to the shipments table).

now...i have an issue on how i'm entering the invoice numbers/values on the form..since the number of invoices might vary with each shipment...i thought abt having a continuous form...i set up one with two unbound fields...invoice number and value...but as soon as i enter data on one row, all the rows become with same value. any feedback on how should i approach this issue?

am not quite sure at what stage would the data in the continuous data be appended to the table..i'd like to have it save to table as soon as a command button in main form is clicked..

appreciate feedback....thanks! :)
 

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
You are going to want to have the Invoices in a SubForm and bound to the query of the Invoice table with Additions allowed. All of the saving will be accomplished automatically by Access. Moving the focus to the SubForm (to enter invoices) will save the MainForm record, and moving the focus back to the MainForm will save the SubForm record.
 

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Thanks for your feedback.

You mentioned that I should have the SubForm bound to the query of the Invoice table with additions allowed. Can you elaborate on this? How would I make the query to make the additions?

I was thinking to make additions to the Invoices table through VBA using the doCmd.RunSql command after the user clicks a command button on the main form.
 

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
No code required! The SubForm would be in Continuous Form mode and the query for the SubForm would simply be a SELECT query. Access would take care of the filtering with the LinkMaster/ChildFields and that would also take care of the ForeignKey in the Invoice table. It is what Access does best!
 

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
thanks ruralGuy!

i managed to set that up as u said...

(1) created a query to load the invoices having shipment ID equal to the one on the main form

(2) attached that query to the continuous form record source

one issue left...when appending new records...the Shipment ID is left as Zero..unless you input it...is there a way to append this automatically? I feel I don't have control over this thing!!! Thats why I like VBA hehe....but I admit this is much easier...just have to figure out how to play around with it ..

Am attaching my sample DB for your reference.

Thanks
 

Attachments

  • db2.zip
    18.5 KB · Views: 178

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
It really should be two separate tables. Here's something to play with.
 

Attachments

  • SubForms.zip
    13.9 KB · Views: 138

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Now would like to go a step beyond that....

I'd like to be able to enter a list of import charges that relate to each shipment...the list of charges might change thru time so i'd like to have the list of charges in a table and loaded with each shipment. the user would then enter the amount for each of the charges loaded.

i have created two new tables: (1) list of charges and (2) charge amounts to the shipments. also i created a new subform to the main form and a query with it. however am having a prob since when no charge amounts are stored for a particular shipment, no charges are loaded (since the shipment ID does not match as there is none in the charges table).

I am uploading the revised DB i have....

any feedback highly appreciated...thanks :)
 

Attachments

  • db2.zip
    18.5 KB · Views: 132

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
You're right....i'm attaching again....
 

Attachments

  • SubForms.zip
    13.5 KB · Views: 125

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
That's better. Sorry but I've got to leave for a few hours. I'll look at it when I get back.
 

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
Is this sort of what you had in mind?
 

Attachments

  • SubForms2.zip
    14.6 KB · Views: 149

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Thanks but not quite what i had in mind...might have not explained myself well...will try again!

I have a list of charges in the 'tblCharges' table such as Freight Charges, Documentation, Communication Chgs. I would like to let the user have the option to enter a value for each of these charges for each shipment. Thus, I want that in the form the system displays all the charges that are in the 'tblCharges' table and then the user will enter the respective values (for each shipment).

I know that one option is to set these fixed on the form as text boxes and have a field for each charge in the Shipments table. However, in this way the system would not be flexible in case I'd need to add charges in the future. If the charges are loaded from a table, creating a new charge would just be a matter of adding one row in the table.

Thanks again and hope I have explained myself better...:rolleyes:
 

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
So if I understand you correctly, as it stands right now the tblChargesAmt table will have 6 records for each shipment in the tblShipments table since tblCharges currently has 6 records.
 

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Yeah that's right....for each shipment in tblShipments table, there will be six records in tblChargesAmt table. Another option would be to append rows to tblChargesAmt table if values are > 0. Thus, when the user does not enter a figure for 'communication chgs', that entry can be omitted from the tblChargesAmt table....but either would be ok...

Thanks..
 

WCOK2

New member
Local time
Today, 02:57
Joined
Oct 14, 2006
Messages
6
I exported your db to a new one since when I opened it, the main form and the subforms won't allow to add new record with the warning your db is read only. In the exported one, I was able to see bottom of main form now shows Records 1 of .. * 6. With the star there, I was able to add new record 7, and input the record. Now I move back to Record 1--6, I can see add new record * also available for the subforms.

Is that the problem you are talking about?
 

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Am afraid that's not the issue no...thanks anyway....

RuralGuy refer to my previous post..many thanks
 

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
...for each shipment in tblShipments table, there will be six records in tblChargesAmt table. Another option would be to append rows to tblChargesAmt table if values are > 0. Thus, when the user does not enter a figure for 'communication chgs', that entry can be omitted from the tblChargesAmt table...
It sounds like you have a plan Ray. It is an interesting programming challenge that will almost certainly involve coding. Post back when you need a nudge to get past a tough spot.
 

ray147

Registered User.
Local time
Today, 09:57
Joined
Dec 13, 2005
Messages
129
Thanks...however still can't figure out something with continuous forms...

Let's say I have a table 'tblCharges' having five rows. I want the continuous form to list one field from all the records from 'tblCharges' along with an empty text box for the user to fill in. The user will fill in the amount for each particular charge. A screen dump of what I'd like to have is attached. Is this at all possible?

Or maybe another idea?

Thanks :)
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    66.2 KB · Views: 93

RuralGuy

AWF VIP
Local time
Today, 03:57
Joined
Jul 2, 2005
Messages
13,826
If you need the ability to add additional records then I would probably use a temp table and append it to tblChargesAmt table when it is complete. Otherwise just append the 6 records directly to the tblChargesAmt table and add the current ShipID from the MainForm. If you use the first method then you will need some clever code like ghudson's better mouse trap to signal moving off of the mainForm record.
 

Users who are viewing this thread

Top Bottom