Writing values from a cascaded combo box

[siuesiue]

Hello Friends

I am a new member

I have been working on an access project for months, and I am stuck on one problem and I desperately need help, hence this email

I have a pair of combo boxes which I am cascading, using the cascading combo boxes principle, however I have one problem

After combo box B is populated based on the selection on combo box A, I want to record the selection of Combo box B into a table, which I am no able to achieve.

once I put the destination table in the Record Source, my combo boxes go crazy


What I am trying to create is an invoicing form

Combo boxA selects product category, ComboboxB has the specific products in that category populated, then I want to select a specific product, and record it into my invoice table, so that whenever I want to pull up the invoice, I can see what products were ordered.
If you need more specific information I can provide the same

Thanks in advance for your help

 

[John Big Booty]

Any chance of posting a cut down version of your DB, with the portion that's giving you grief?

 

[siuesiue]

Hello John

Thanks so much for your reply

I am attempting to attach my file with this post

The combo boxes in question are the ID_CategoryID and ID_ProductID and they are on the form titled 'Invoice Details Sub Form'

 

[John Big Booty]

You will always have trouble with a cascading combo box set on a continuous form. The problem is that Access is unable to treat the controls separately so as the focus shifts from record to record in the continuous form what they contain will be a reflection of the currently highlighted record rather than their true value

The way I would get around it is to have a separate form for loading items into the invoice. Have a look at the attached DB. I've left a few things undone but you should be able to see what I've done and carry on from there, if you wish.

 

[siuesiue]

Hello John

I cannot thank you enough. I was unaware of this limitation, and probably would never have figured this out without your help.

I see what you have done, and I like your work around

But I had a question, everytime I try to add an item to the invoice, it brings up a warning, saying it will Requery and append my tables, is that ok? Is it just modifying the invoicedetails table? how do I know what tables it is modifying?

Also, I noticed that some of the ID_CategoryID field in InvoiceDetails Table is empty, though on the form it has values, why would this happen, how do I make sure to avoid it

Thank you so much once again

 

[John Big Booty]

To stop the warning message when you click the add item button go to the Tools Menu, then to Options, in the pop up box go to Edit/Find tab In the Confirm option group uncheck Record Changes and Action Queries.

From memory some of the records where not fully populated in your initial DB. I think that from now on all records should be fully populated. You could either delete all the records and start again from scratch or given the small number you could manually update them.

Also as presented my solution does not populate the tax field and one other that I can't recall at present. However it should be a simple exercise for you to add that functionality.

 

[siuesiue]

Hello John

Thanks again for your prompt reply.

I will disable the messages as you directed. But I wanted to check with you, if I am doing anything wrong because of which I am getting the said message, or is it just because I am writing to the InvoiceDetails Table.

I will complete the other parts like tax field etc. I guess I have a handle of it going forward.

I had one more question though... for printing invoices, if I did not want to print them from the forms page, but instead have a Word or Excel Template in which I want to print, is there a way I can link cells to a particular field in an excel table to fetch a value? Any thoughts?

 

[John Big Booty]

You are getting that message because when you hit the Add Item button it runs an append query that collects the information from that form and then appends it to the Invoice lines/details table. One point about leaving that message on during development of the DB is that it will give you feed back about how your queries are running. For example if you run an append or update query and you are expecting only one record to be appended/updated but the message says that it's about to append/update 2 records, for example, you know that there is a problem with your logic that you need to fix.

As for printing invoices, I would create a report that prints the required invoice. Once you have created the report it is a simple matter to then print it or email it to your client.

 

[John Big Booty]

I've never exported to Excel so can't really comment on the best way to do that.

 

[siuesiue]

Great! Thank you so very much, I could not have moved ahead without your help

I am going to try and complete the project, will ask if any more issues

Thanks once again

 

[John Big Booty]

Not a problem. Good luck and have fun.

 

[siuesiue]

Hey

I noticed on the example that you did for me, that you have linked the invoiceHeader with the subform quite differently, and I am trying to understand it, if you can help.

1. The way I did it was to create the Invoice form, with each record being identified with the invoice number, and I had to create the subform with a record called 'ID number', which linked to the invoice no. helped me to have multiple products for each invoice number. For this I had to create an InvoiceDetails Table, which had the various products, units, rate, tax, and then the Sale price in the query.

2. In your example I noticed that you did not use the ID number, so how did you link the sub form with the invoice number.

3. And so can I get rid of the ID number and the whole, Invoice Detail table, if yes, where do I calculate the Sale price?

4. And finally, I noticed on your invoicedetail form, that as I add a new item, it automatically appends more rows on the form, how did you do that, can you show me? I had it a whole different way (as you can see from my file), and I like your way a lot lot better.

Thanks in advance

 

[John Big Booty]

Hey

I noticed on the example that you did for me, that you have linked the invoiceHeader with the subform quite differently, and I am trying to understand it, if you can help.

1. The way I did it was to create the Invoice form, with each record being identified with the invoice number, and I had to create the subform with a record called 'ID number', which linked to the invoice no. helped me to have multiple products for each invoice number. For this I had to create an InvoiceDetails Table, which had the various products, units, rate, tax, and then the Sale price in the query.

That's pretty much what I've done too. I just haven't shown the ID Number on the Sub form, but it is in the Record Source for the Sub form. Check the Link Child Fields in the properties for Sub Form container. That's the one that appears when you first click on the Sub form when the form set is in design view.

2. In your example I noticed that you did not use the ID number, so how did you link the sub form with the invoice number.

See my previous explanation above.

3. And so can I get rid of the ID number and the whole, Invoice Detail table, if yes, where do I calculate the Sale price?

No, that's what make this whole thing work.

4. And finally, I noticed on your invoicedetail form, that as I add a new item, it automatically appends more rows on the form, how did you do that, can you show me? I had it a whole different way (as you can see from my file), and I like your way a lot lot better.

Thanks in advance

What I've done is add an Add Item button to the sub form. What that does is opens the form FRM_ItemAdd and passes the ID_Number to it via OpenArgs. The FRM_ItemAdd has its own Add Item button that first check that all the field have data in them, then it fires the query QRY_ItemAdd which append the appropriate data into table InvoiceDetails_Table and finally re-queries the invoice details sub form.

Have a look at the various event behind each button, and also on each of the forms, and you should be able to follow what's going on.

 

[siuesiue]

Thank you once again.

I think I am very close to completion, just trying to do a clean up.

Three more questions for you.

1. I noticed you added two queries, QRY_InvItem. and QRY_ItemAdd
I know we need the ItemAdd query, but do I need the QRY_InvItem, when I already have a InvoiceDetails_Query? I tried to delete the QRY_InvItem and link the FRM_ItemAdd to InvoiceDetails_Query.
But it did not work for me, not sure what I am missing. (sorry, I am not good with this at all)

2. I changed the view of the FRM_InvoiceSubForm to continous form, and have it the same format as your example. But the only thing is on the subform, the spacing between each line item is quite large, and I could not figure out which parameter do I need to change to reduce the space between each row of item in the subform (I spent way too much time but no luck)

3. And additionally, I am not familiar with the open arguments which you used to pass data betwen the item add and the invoicedetails subform. Why do we need to do that, and what exactly is happening?

If it will help, I can send you the database again to see how it looks now.

Thanks again for your help, I am really understanding this a lot better now

 

[John Big Booty]

Thank you once again.

I think I am very close to completion, just trying to do a clean up.

Three more questions for you.

1. I noticed you added two queries, QRY_InvItem. and QRY_ItemAdd
I know we need the ItemAdd query, but do I need the QRY_InvItem, when I already have a InvoiceDetails_Query? I tried to delete the QRY_InvItem and link the FRM_ItemAdd to InvoiceDetails_Query.
But it did not work for me, not sure what I am missing. (sorry, I am not good with this at all)

........

FRM_ItemAdd has a cascading Combo box set on it QRY_InvItem selects a set of PD_ProductName depended on CT_Category. Whilst QRY_ItemAdd appends the selection from FRM_ItemAdd to your invoice details. Both these queries serve very different functions so deleting one will effectively break the DB.

Thank you once again.

.......

2. I changed the view of the FRM_InvoiceSubForm to continous form, and have it the same format as your example. But the only thing is on the subform, the spacing between each line item is quite large, and I could not figure out which parameter do I need to change to reduce the space between each row of item in the subform (I spent way too much time but no luck)

..........

I'm pretty sure that the example I posted had the sub form in continuous view. It sounds like what you need to do is to drag the bottom of the form details area up to make it just tall enough to show the fields you want. This is done whilst the forms are in design view of course.

......rm (I spent way too much time but no luck)

3. And additionally, I am not familiar with the open arguments which you used to pass data betwen the item add and the invoicedetails subform. Why do we need to do that, and what exactly is happening?

If it will help, I can send you the database again to see how it looks now.

Thanks again for your help, I am really understanding this a lot better now

OpenArgs is the last argument in the DoCmd.OpenForm statement. You can use the OpenArgs to pass information from the initiating form to the form being opened. This information can be checked in the OnLoad event of the form being opened. In the example DB I posted earlier I used it to pass the ID_Number between the two forms. You can also use the OpenArgs to control how a form behaves under various conditions or what data is shown on the form.

 

[siuesiue]

Hey John

Thanks for the quick response.

I am clear on question 2 and 3, but I guess I did not explain myself well on question 1. I meant to ask, that I have an existing query called 'InvoiceDetails_Query' so instead of creating one more called 'QRY_InvItem', cant I instead use the existing query, and make the combo boxes on the FRM_ItemAdd viz the PD_ProductName and CT_Category, look at the 'InvoiceDetails_Query'.
So that I can have one less query.

I am attaching my DB so that you can look at what I am trying to do.

Also, there is one issue on the form, it works fine if I have to add items to an existing invoice, but if I create a new invoice and I am trying to add the first item it gives me an error 'Invalid Use of Null'

And finally, since now I cannot make any changes to the form directly, but use the Frm_AddItem to add a new entry, how do I modify an existing entry or delete an existing entry?

Thanks in advance

 

[John Big Booty]

OK sorry about the misunderstanding. whilst the two queries are similar I'd be very careful about deleting either of them. However if you are keen to get rid of one, what you should do, is do some testing. First change the name of the query you wish to delete (add a one to the end of the name), then correct all the references to the renamed query to the query that remains. then see what effect that has on the DB. I Would suggest doing this testing on a back up of the DB just in case, the wheels fall off in a big way.

The problem with the 'Invalid Use of Null' is because I was trying to grab the linking field from the SubForm, and off course before the first item has been added there is nothing for the code to grab. Copy and paste the following (in it's entirety) over the OnClick event for Command20

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    [B][COLOR="Green"]stLinkCriteria = Me.Parent.InvoiceNo[/COLOR][/B]

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

Exit_Command20_Click:
    Exit Sub

Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
    
End Sub

alternatively (just copy and paste) the Green bolded section is what I have changed, so now the code is getting the link from the main form now.

For the last part of the question give me a moment and I'll make some changes that will allow amendments and deletions.

 

[John Big Booty]

Hi siuesiue

I've fixed the Null thing and added the amend delete functionality to my original DB. Have a play with it and see what you think.

 

[siuesiue]

Hey John

Thanks again, I used the code you gave me, and it works like a charm,

1. For the amend/delete functionality, I made the changes on my DB, but I have an issue with the checkbox. When I click it on the form, it does not check it (it kinda greys it out or puts like a white highlight on it). I tried to check it from the table, and it works fine but not from the form
I did a search on it, and I found one other person who had the same issue, and he was suggested to recreate the form, after which it worked fine.

But I wanted to check with you first to see if you had any suggestions, or should I try re-creating the form.

2. On the VBA code for the FRM_InvDetails there is a piece of code,

Private Sub Combo12_AfterUpdate()
Me.Combo14.Requery
End Sub

Private Sub Form_Current()
Me.Combo14.Requery
End Sub

I could not figure out, where the Combo12 and Combo14 object comes from, as the FRM_InvDetails does not have any combo box on it. Can you help

Thanks

 

[John Big Booty]

Hey John

Thanks again, I used the code you gave me, and it works like a charm,

1. For the amend/delete functionality, I made the changes on my DB, but I have an issue with the checkbox. When I click it on the form, it does not check it (it kinda greys it out or puts like a white highlight on it). I tried to check it from the table, and it works fine but not from the form
I did a search on it, and I found one other person who had the same issue, and he was suggested to recreate the form, after which it worked fine.

But I wanted to check with you first to see if you had any suggestions, or should I try re-creating the form.

Also have a look at the queries I've added to deal with the new buttons and field.

........

You will need to add a Yes?no Filed to your Invoice details Field, and also the query that populates the Invoice details subform. You will also have to set the Allow Edits property for the Invoice details sub form to yes. All fields on the subform, other than the Yes/No should be set to Enabled = No, Locked = yes. The Yes/No filed should be the opposite.

Hey John

..........

2. On the VBA code for the FRM_InvDetails there is a piece of code,

Private Sub Combo12_AfterUpdate()
Me.Combo14.Requery
End Sub

Private Sub Form_Current()
Me.Combo14.Requery
End Sub

I could not figure out, where the Combo12 and Combo14 object comes from, as the FRM_InvDetails does not have any combo box on it. Can you help

Thanks

Sorry, that is an artefact of an earlier incarnation of the form. I thought I'd deleted all the extraneous code, but have obviously missed some.