Auto-completing comboboxes and empty tables

[New2VB]

Hi all,

A bit of help if someone has a moment.

If you launch the "Company" form in the attached db (examples are better to work with than explanation) you can see it is 1 form with 2 subforms.

If you double-click on one of the names in the Employees subform a pop-up form "NewOrders" is loaded. However, once the data has been entered and the close button has "OnClick" written the data to the Orders table a 2 problems occur.

1. The "order" is duplicated in the Orders table
2. The Orderssubform isn't requerying.

I have checked the relationships and the Master-Child field settings but can't work it out. Can someone help?

 

[Poppa Smurf]

1. The "order" is duplicated in the Orders table

The order is duplicated because you have bound the NewOrders form to the Orders Tables and also using code to append a new order. This means the data entered in the NewOrders is stored in the table, then you are adding the same data using code.

Remove the record source from this form and make sure the all the fields are unbound.

2. The Orderssubform isn't requerying.

It is requerying but the problem is that you are missing the companyno in the orders table when you are adding a new order.

Add the highlighted line of code in the Orders form
tb.AddNew
tb!Employee = Me.Combo26
tb!companyno = Me.Combo26.Column(1)
tb!ContactNumber = Me.Text28
tb!Order = Me.Order
tb.Update

Finally, may I suggest that you use meaningful names for your text boxes and combo boxes etc. Later on when you try to debug it will be hard to remember why Combo26 is used. You could change Combo26 to Employee etc.

 

[New2VB]

Fantastic!!!

Thank you Poppa Smurf that's brilliant. I had not remembered that I had been lazy and generated and AutoForm from the Orders table instead of creating it from scratch (thus the duplication of the 'sources)

Thanks also for your advice on correct naming conventions of textboxes & comboboxes. This is a test db and therefore will be disposed of once the production db goes into use.

I created the test db because I find it easier to demonstrate the problem I am having instead of trying to explain it when I need help from gurus like yourself. I also prefer to try new procedures and functionality on a test than on a production or split db.

Once again many thanks for your help, no doubt I will be posting another issue soon

 

[Poppa Smurf]

Thank you for your compliments.

 

[New2VB]

Hi Poppa Smurf,

No problem, thanks for the help.

Now I wish to further complicate the issue. I would like to be able to double-click on a name in the Employees subform and use that double-click event to launch the Orders form (as it does already) and have combo26 (UserName) and Text28 (ContactNumber) auto-populated.

I have tried this
hxxp://bytes.com/topic/access/answers/192500-double-click-record-subform-open-record-new-form

but can't get it to work.

Any ideas?

 

[Poppa Smurf]

I did not use the link in your previous email.

Please find attached an updated version based on your requirements with changes to your previous version.

1. Changed combo26 to a text box
2. Set the default value of combo20 to [Forms]![Company]![employees_subform]![name]
3. Set the default value of text28 to [Forms]![Company]![employees_subform]![contactnumber]
4. Added text box Company Id
5. Set the default value of Company ID to [Forms]![Company]![list4]

Steps 4 and 5 are required as the combo box used to select name has been removed and we need to obtain the company id for use when adding the record.

 

[New2VB]

Hi Poppa Smurf,

Thanks for that but am I missing something? How do I now select an employee from the Orders form? I tried the double-click/auto-populate scenario I was talking about in my previous post but it doesn't work.

 

[Poppa Smurf]

I would like to be able to double-click on a name in the Employees subform and use that double-click event to launch the Orders form (as it does already) and have combo26 (UserName) and Text28 (ContactNumber) auto-populated

I made the changes as per your requirement as above. When you click on the employee in the Employees subform the Employee and Contact number are displayed in the Order form. Is there any reason why you want to change an employee after selecting an employee from the subform?

 

[New2VB]

Hi,

Ummmm...no it doesn't.

I'm launching the Copy of Company form, selecting Southwind, double-clicking on Charley. "Orders" loads but the CompID, Combo26 & Text28 fields display #Name?

or am I being an idiot? (Which under the circumstances is more than probably true...

 

[Poppa Smurf]

Sorry, Use the Company form, I should have removed the copies.

The copies are just copies of the original forms in case I had problems after I changed the originals.

 

[New2VB]

Hi,

Same procedure, I'm launching the Company form, selecting Southwind, double-clicking on Charley. "Orders" loads but the CompID, Combo26 & Text28 fields are
blank.

BTW. What time is it there?

 

[Poppa Smurf]

Try this one, Select Company then employee an d the fields will be displayed.

The time here is 2105 on 23rd July

 

[New2VB]

Hi,

I'm really sorry if I'm being dense but after I open the "Company" form, select a company from the listbox and double-click a user name the "Orders" form does launch but the fields are empty.

Looking at the double-click event code I can't see how the fields are populating.

DoCmd.OpenForm "NewOrders", acNormal, List4, , acFormAdd
' DoCmd.OpenForm "NameOfDifferentForm", , , "[OrderDetailsID] = " & Me!
OrderDetailsID
' DoCmd.OpenForm "neworders", , , "[employee]=" & Me!Name

 

[Poppa Smurf]

From my earlier post

Please find attached an updated version based on your requirements with changes to your previous version.

1. Changed combo26 to a text box
2. Set the default value of combo20 to [Forms]![Company]![employees_subform]![name]
3. Set the default value of text28 to [Forms]![Company]![employees_subform]![contactnumber]
4. Added text box Company Id
5. Set the default value of Company ID to [Forms]![Company]![list4]

Steps 4 and 5 are required as the combo box used to select name has been removed and we need to obtain the company id for use when adding the record.

The fields are populating using the default values see points 2,3 and 5 above.

DoCmd.OpenForm "NewOrders", acNormal, List4, , acFormAdd

The above code just opens the Order form.

In my version I did the following using the Company form
1. Select Company : Southwind
2. Select Employee : Charley
3. Double click on Charley
4. New Orders form is displayed with
2 in CompID
Charley in combo26
456123 in Text 28

Check that the default values are in the fields on the Orders form

 

[New2VB]

Hi,

Please pardon the delay. I'm using Access2003, which release are you using?

I can see what it is supposed to do, it's just not doing it. I am working on another priority now as one of the production dbs isn't working properly, si I 'll get back to test02 as soon as I can.

 

[Poppa Smurf]

It is your Access 2003 database with the latest changes. I used Access 2007 to make the changes. Access 2007 did not convert the database to 2007.

I tested the database on my other computer using 2003 and did not have any problems creating new orders.

 

[New2VB]

Hi PoppaS,

It was the machine/Access install..

I tried it on another machine and it worked perfectly, thanks for the help.

Regards,

 

[New2VB]

Tries to add to your rep but got this message "You must spread some Reputation around before giving it to Poppa Smurf again."

Not sure what it means so my thanks will have to do for now.

'til we meet again...

 

[Poppa Smurf]

Thank you. I am glad you were able to solve the problem.
Thank you also for giving me the chance to learn a bit more using access.