Please Help Me!

[steve09]

I go to "Forms" > "Place an order form" which comes up with two boxes...
"Enter customer name" and "Enter customer number"
Once correct details entered the relevant form comes up, in this case I used name "Stephen" and customer number "1"...

I then click on the button "Place an order" which brings up this form...

In relation to my 1st post, the CustomerNo HAS transferred boxes so now my question has been answered...But...

Once I have entered details like this...

and clicked the save button, it all saves into the "Order" table, which is where it's meant to be saved...But...
As you can see, the customer number is the only thing that HAS NOT been saved...

In conclusion to not saving the CustomerNo in the "Orders" table...
It has not linked that order with CustomerNo 1 who is Stephen...

So in design view of the "Orders" table I enabled Required to "Yes"

So when I entered data again on the "Place an Order" form it came up with this little error message...

With that little error message it won't let me save the order information because it's saying there is no CustomerNo in the "Orders" table and I can not leave it blank, but as you can see in the "Place an order" form...The CustomerNo is filled in :S

Oh yeah, I have not done a school boy error of not linking the "Orders" table to this form!

Please help me
Steve.

 

[RuralGuy]

What code do you have under the "Place an order" button?

 

[steve09]

What code do you have under the "Place an order" button?

Thanks for replying mate!
This is whats under the "Place an order" button...

Private Sub cmdOrder_Click()
On Error GoTo Err_cmdOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Orders"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOrder_Click:
Exit Sub

Err_cmdOrder_Click:
MsgBox Err.Description
Resume Exit_cmdOrder_Click

End Sub

This may help you too, this is the text I have in picture 2's "CustomerNo" text box...

=Forms!PlaceAnOrder!CustomerNo

Thats what I have done to transfer the value to the text box which is all good, but then when I save the data, it picks it up as nothings in there, even though you can clearly see there is as shown in picture 5

But when I take this...

=Forms!PlaceAnOrder!CustomerNo

off the "CustomerNo" text box in picture 2 and enter a CustomerNo manually, it saves it to the correct customer, but my Coursework task says it has to be entered automatically from the Customer form which I have came from, so unfortunately I can't enter the CustomerNo manually.

Cheers RG!
Steve.

 

[GaryPanic]

what is the field format of the tables
ie client number format and orderclientno
are they the same ???(in tables)

check
its probably not this - but check start with the simple things first
makes sure they are both the same format ie number, number not number , text

also put a save option in just to be safe

 

[steve09]

what is the field format of the tables
ie client number format and orderclientno
are they the same ???(in tables)

check
its probably not this - but check start with the simple things first
makes sure they are both the same format ie number, number not number , text

also put a save option in just to be safe

Hi mate

The "CustomerNo" field on the "Customer" table is Autonumber
The "CustomerNo" field on the "Orders" table is Number

Is this ok?

Thanks, Steve

 

[RuralGuy]

Rather than putting this assignment in the ControlSource of the control:
=Forms!PlaceAnOrder!CustomerNo
...Just bind it to the CustomerNo field. Then in the OnLoad event of the 2nd form put: Me.CustomerNo = Forms!PlaceAnOrder!CustomerNo

You will need to use the code builder to place the code I supplied.

 

[RuralGuy]

The "CustomerNo" field on the "Customer" table is Autonumber
The "CustomerNo" field on the "Orders" table is Number

Is this ok?

Yes! The Orders table Number field must be a Long Integer field, which is the default when you specify a Number field.

 

[steve09]

Rather than putting this assignment in the ControlSource of the control:
=Forms!PlaceAnOrder!CustomerNo
...Just bind it to the CustomerNo field. Then in the OnLoad event of the 2nd form put: Me.CustomerNo = Forms!PlaceAnOrder!CustomerNo

You will need to use the code builder to place the code I supplied.

RG = Legend!
I have been pulling my hair out for a day or so over this, thank you so much!
It's all working perfectly now

I'm hoping you can help me out on this one RG...

I need to calculate the total cost of the order and enter this into the "CostOfOrder" field which you can see in picture 3.

I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.

Here is the query for inclusion in VBA...

Dim StrSQL As String

StrSQL = "SELECT orderline.qty*sandwich.price"
StrSQL = StrSQL + "FROM sandwich INNER JOIN orderline ON
sandwich.sno=orderline.sno"
StrSQL = StrSQL + "WHERE (((orderline.orderno)="
StrSQL = StrSQL & orderno
StrSQL = StrSQL + "));"

Do Until recordSetData.EOF
Total = Total + recordSetData.Fields(0).Value
recordSetData.MoveNext
Loop

I think the Query is that, but where do I put this in the form to make it work?

Thanks mate!
Steve

 

[RuralGuy]

I have been pulling my hair out for a day or so over this, thank you so much!

I've always enjoyed the hair pulling stage since it meant I was about to learn something I didn't know!

Is it a course requirement that you accomplish the task in this manner?

I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.

Queries can do calculations and Aggregate queries can, well, aggregate stuff (do sums).

 

[RuralGuy]

Another thing. When you concantenate strings such as you are doing with the StrSQL string, it is always a good idea to do a MsgBox StrSQL before you use it to see what you've got. You can always delete that line after you are satisfied as to the content of your string.

Edit: You should also have Option Explicit at the top of your class module to catch any typos and force you to Dim all of your variables!

 

[steve09]

Is it a course requirement that you accomplish the task in this manner?

1) The calculating of the order?
or
2) The asking on the Internet for help

With regard to my calculating query, where would I put that?
I have no idea what so ever!

Edit - I did not create that query. Our Tutor has supplied it for us in the sheets of paper we are working from.

Steve

 

[RuralGuy]

I don't have an issue with asking for assistance on the internet. I take it you do not *have* to use a loop on a recordset to get the answer?

 

[steve09]

I don't have an issue with asking for assistance on the internet. I take it you do not *have* to use a loop on a recordset to get the answer?

I just didn't understand your reply! of "Is it a course requirement that you accomplish the task in this manner?"

I don't know about not having to use the loop on the recordset to get the answer, but I think we have to use one.

This is what it says from our sheet...

"To complete the order, you could calculate the cost of the order and enter it into the orders table automatically. To do this you need to read all the orderlines for an order into a record set and calculate the cost of each orderline. You then need to use a loop in VBA to add the all together."

Cheers RG!

 

[RuralGuy]

Have you done a MsgBox on your StrSQL variable yet? You should have seen some MISSING spaces in the text. Try making your query in the Query builder. You can learn a lot about queries by using the query builder. You can then switch to Datasheet view and see what you will get with that query.

 

[Rich]

This is what it says from our sheet...

"To complete the order, you could calculate the cost of the order and enter it into the orders table automatically. To do this you need to read all the orderlines for an order into a record set and calculate the cost of each orderline. You then need to use a loop in VBA to add the all together."

Cheers RG!

WRONG! your tutors need sacking

 

[RuralGuy]

Does it say anything in your book about *not* storing calculated values?

 

[steve09]

Does it say anything in your book about *not* storing calculated values?

No, what I have typed is what it says
I don't know how to do this part at all.

Steve.

 

[Rich]

A Totals query is the fastest and correct method for obtaining an Order total.
It's up to a 1,000 times faster than a loop through the recordset.

 

[steve09]

A Totals query is the fastest and correct method for obtaining an Order total.
It's up to a 1,000 times faster than a loop through the recordset.

Could you be as kind to help me out by doing one for me?

What information do you need off my database (If any?)

Thanks Rich and RG so far!
Steve.

 

[Rich]

I don't mind helping if you get stuck, I don't have the time to do the work for you.
Access ships with many sample DBs, an orders sample is included that you can use to learn from