Order form problem

[Gazza2]

Hope someone can help with a strange problem.

I have a form(form1) which is used to add details, when i have finished entering the details i press the enter key which then opens up another form (frmOrders) and the supplier code is passed to this form(this bit works).The focus is set on the part no text box so i can enter products but when i enter a quantity for the product(in another text box) it tells me that a related field in tblorders is required. This related field is the supplier code field which is recieved from the previous form.

If i try to requery the form from any event then it lets me go to the next line but it only updates the first record int the table.

I have tried to use DoCmd.gotorecord,,acnewrec but this doesnt work.

thanks in advance

Stressed in the uk

 

[boblarson]

Just put this line in just before the code that opens the other form:

If Me.Dirty Then Me.Dirty = False

That should save the record before opening up the next form.

 

[Gazza2]

Thanks for the quick reply bob but i dont think i made myself very clear.

The Problem is on the second form when it opens with the supplier code in the text box it doesnt register that there is something in the text box so i have to manually retype the supplier code to get it to register.

 

[boblarson]

Thanks for the quick reply bob but i dont think i made myself very clear.

The Problem is on the second form when it opens with the supplier code in the text box it doesnt register that there is something in the text box so i have to manually retype the supplier code to get it to register.

What code are you using to open the other form?

 

[Gazza2]

the form is opened after i select a row from a list box withthe following code in the afterupdate of the text box.

DoCmd.OpenForm "FrmPurchaseOrder"
[Forms]![FrmPurchaseOrder]![SupplierCode] = Me.LstSupplierSequence.Column(1, TxtSelect.Value - 1)

 

[boblarson]

Okay, what you need to do is to open the form for that code using:

DoCmd.OpenForm "FrmPurchaseOrder", acNormal, , [SupplierCode] = "Me.LstSupplierSequence.Column(1, TxtSelect.Value - 1)

And then you can assign the value:

[Forms]![FrmPurchaseOrder]![SupplierCode] = Me.LstSupplierSequence.Column(1, TxtSelect.Value - 1)

 

[Gazza2]

Ok im getting a serious headache with this now.

I have attached a copy of my db in the hope that someone can spot where ive gone wrong.

The problem is that whatever i do i either get an error about LinkMasterFields or the order detatils dont show in the purchase order table.

I know its a link problem somewhere but cant seem to find it.

 

[boblarson]

Okay, several things wrong.

1. You had tables included in the recordsource which shouldn't have been.

2. You had controls that needed to be combo boxes and then the text boxes to show the other parts refer to them.

3. Your Master Child Links were wrong and needed to just be PurchaseOrderID.

Check out what I did and see if you have questions. I also changed the form from Data Entry - YES to NO so that you could see the data. If you don't, every time you open the form you won't see anything. The subform should not be set to Data Entry Yes but to NO all of the time so that you can see the records that exist on that purchase order.

I may be forgetting a few things but here you go.

 

[Gazza2]

Ok thanks for all the help so far.

The form that i am working on is not for checking po`s but for entering them so i think i may have mislead you.

Basically what i wanted to do was for someone to enter a supplier in the posupplierselect form and when they select the correct supplier it opens the FrmPurchaseorder form so they can then enter the products that they wish to buy from that supplier.

This form will have alot more features once i have the basics working.

P.S. May i ask why the controls must be combo boxes(not a fan as the look ugly ,LOL)

Thanks
Gareth

 

[boblarson]

Ok thanks for all the help so far.

The form that i am working on is not for checking po`s but for entering them so i think i may have mislead you.

Basically what i wanted to do was for someone to enter a supplier in the posupplierselect form and when they select the correct supplier it opens the FrmPurchaseorder form so they can then enter the products that they wish to buy from that supplier.

I'll try to revisit this when I can - I was out sick from work yesterday so I don't know how much time I'll have.

P.S. May i ask why the controls must be combo boxes(not a fan as the look ugly ,LOL)

I guess beauty is in the eye of the beholder as I don't think their ugly, but either a combo or list box is best so that people don't have to ensure that they type something exactly correct for the database to find it. If you have a text box and you have a business named "Kore Processing" for example and someone has never seen the name, they may type "Core Processing" and the database would not be able to find it. But using a combo they can see that there is no Core listed so they might look for alternate spellings.

 

[Gazza2]

Thanks for the info and help bob

 

[Gazza2]

ok i can get it working if i remove the Enforce referential integrity from the relationship window but im thinking that is not a good idea.

 

[boblarson]

ok i can get it working if i remove the Enforce referential integrity from the relationship window but im thinking that is not a good idea.

I guess I'm a bit lost as to what isn't working. Can you forgive an aging Access developer and restate what isn't working - what needs to happen? I can then take a look at the sample again and try to see what needs to be done.

 

[Gazza2]

firstly its not the ageing access developer thats the problem it the learner access developer(me) thats the problem LOL.

Secondly to the problem at hand

Basically this form is to be used to enter purchase orders which once working will be e-mailed to the suppliers.

The problem im having is that if I try to enter a product into the subform I get an error telling me that a related field in TblPurchaseOrders is required.I have tried many different settings in the relationships,tables and the form itself and the only way i can get it working the way i want after taking your previous advise into account is to remove the Refferential integrity from the 1 to many relationship for the PurchaseOrderID join.

Hope this makes more sense to you than it does to me.

Thanks
Gareth

 

[boblarson]

So, it does this error on the database sample I provided, correct? If so, I'll take another look and see what I can see.

 

[boblarson]

I just went back to it and it seems fine. I can add a new PO and add PO Line items using frmPurchaseOrder2. Can you upload what you are currently using?

 

[Gazza2]

Here is what im working on (please dont tell me off for changing the combo`s)

 

[boblarson]

Get rid of tblSuppliers in your underlying query. That is what is causing it not to be able to write.

 

[boblarson]

Also, I go back and say why do you think a combo box is ugly when it is way more functional than what you have here. With the combo you can display the name easily enough, the user doesn't need to remember ALL SUPPLIER CODES as they just select from a list, and your underlying table updates just fine. What you've gone to is not efficient, it makes it so the user has to know the code exactly.

So, I know you didn't want a lecture on that, but essentially your form is in the "dark ages" so-to-speak.

 

[Gazza2]

Tried that and it made no difference (I only added that so i could get the supplier name to show)

Ok i see your point with the combo`s , That was what i was using the FrmsupplierSelect form for to search the suppliers based on a few letters.

im basically just using the sales app from work (not access) as a guide to help give me an idea of what to do( and that is very dark ages in computer terms (over 10years))