Problems with continuous forms (1 Viewer)

ray147

Registered User.
Local time
Today, 12:08
Joined
Dec 13, 2005
Messages
129
I am having problems with Continuous forms....I had already started this in another thread but now i restructured a bit and better to start new...

Attached i'm attaching a sample DB with what I have...

In Form1, i have a subform subInvoices....in the footer of the continuous form i have a total that sums the invoice amounts. i'd like that when the user clicks the command button, that total is transferred to the text box 'value of goods FCY'. i tried doin it as u can see in the DB but i'm getting an error...

also, i'm still not feeling comfortable with the updating of the continuous form table 'SInvoices'. i'd like the changes to the subform are updated to the table when the user clicks the command button. is this at all possible? also is it possible to update certain fields in the table that are not mentioned in the form..for example i'd like to remove the columns currency and shipment ID from the subform and these would be taken automatically from the main form controls (Shipment ID and Invoice Currency respectively).


I appreciate very much your feedback.

Thanks:)
 

Attachments

  • ray147.zip
    48.5 KB · Views: 134

ray147

Registered User.
Local time
Today, 12:08
Joined
Dec 13, 2005
Messages
129
got 18 views to my thread and no one can help?! :confused:
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Sep 12, 2006
Messages
15,613
the syntax you are using for referencing the field in the subform is wrong - if you are in the parent it should use the bang operator, not the dot operator.
as you are in the parent, you don't need to explicitly reference the subform. I have not checked whether the syntax you used for that was right or not.

this line pops up the value from the subform

Private Sub Command143_Click()
MsgBox subInvoices!txtInvTotal
End Sub

so to do what you want you just need
txtinvalue = subInvoices!txtInvTotal

It wasn't clear to me exactly what you were trying to do with everything, so I can't really address the specific questions you raised. Suffice it to say that generally you can pretty well do anything you want within Access - its just a matter of designing your tables correctly to facilitate this.
 

ray147

Registered User.
Local time
Today, 12:08
Joined
Dec 13, 2005
Messages
129
OK will try to explain better what i'd like to do...

1. user enters the container number in the text field on main form

2. user chooses the invoice currency from the drop down box on main form

3. user enters the invoice numbers/values relative to that shipment (on the subform)

4. when the user clicks the command button:

- the Value of Goods text field on main form updates to the invoice amount totals

- the invoice details are appended to the 'SInvoices' table .... the Currency and Shipment ID columns to be taken automatically from the main form (not let the user enter these fields on the subform - i'd like to eliminate those two columns from the subform)


of course you'd have to refer to the database which I attached on my first post.

Many Thanks.
 

boblarson

Smeghead
Local time
Today, 05:08
Joined
Jan 12, 2001
Messages
32,059
I just looked at your database and in the click event of the button I fixed the syntax to show you the message box and then update the text box on the main form.
Code:
 MsgBox Forms!Form1.subInvoices.Form.txtInvTotal
 Me.txtInValue = Forms!Form1.subInvoices.Form.txtInvTotal
 

ray147

Registered User.
Local time
Today, 12:08
Joined
Dec 13, 2005
Messages
129
Thanks Bob ...
I sorted that one out an am attaching a revised DB...

i still have issues which i cannot resolve though....i have managed to get over a lot of issues with access and VBA but i just can't really understand how can i control the updating of continuous forms....

as you can see on the attached DB, in the continuous sub form, i have four columns: ShipID, Invoice No, Currency, Value. Whenever the user updates this subform, the table 'SInvoices' is updated accordingly. However, I would like the update to take place ONLY when the user clicks on the command button. Is this possible?

Reason being that I'd like to have control of what's being appended to the table. I would like to remove the Currency and ShipID columns as those are standard for all the rows and can be read from the main form.


Look forward to some feedback. Many thanks guys!
 

Attachments

  • ray147.zip
    46 KB · Views: 127

boblarson

Smeghead
Local time
Today, 05:08
Joined
Jan 12, 2001
Messages
32,059
Hopefully I'm understanding you correctly and so I hope this helps:

When you have a main form and a subform, the record on the main form will automatically update when you enter the subform. There is no way around that as a subform is linked to the main form and Access knows that whatever changes have occurred on the main form must be saved in case you change something or add something related on the subform. So, if you are trying to update something on the subform, but don't want the main form updated until the subform changes are made, then you may have to rethink your design and swap which is the main form and which is the subform.

As for removing columns in the subform, you won't be able to completely remove them if the data must flow into them from the main form. You can, however, hide them so they don't show.

Now, since I won't be able to view the database again until I am off work, and I won't be able to tonight anyway as I have to go somewhere after work, hopefully the above will help. If not, I apologize as I haven't been able to take enough time to fully analyze your questions alongside your database.

I answer questions here in between things I'm doing at work as it gives me a chance to "clear my head" from the projects I'm currently working on.
 

rhett7660

Still Learning....
Local time
Today, 05:08
Joined
Aug 25, 2005
Messages
371
Bob..

I figured it out.... It had to do with the inner and left joins... I changed the joins in the query to "Left" and it now works..... Man I have been staring at the code for a couple of hours.. and then it just hit me.....

Thanks again for taking the time to take a look at it!!

Thanks
R~
 

boblarson

Smeghead
Local time
Today, 05:08
Joined
Jan 12, 2001
Messages
32,059
Glad to hear! I hate it when I do the same thing, so I totally understand.
 

Users who are viewing this thread

Top Bottom