Data not appearing now in form

[Waterdog]

Hey guys, I've been putting together an Access project for processing product returns. Gotten some great help on the forums for which it's working quite nicely now. However, I hadn't worked on the program in a couple weeks and tried using it today. For the most part, it functions well. Still little tweaks here and there I want to do to make it work better for my needs. But I noticed something that I'd swear it wasn't doing when I last worked on it. Below is a screen cap of the form.



OK, so what you're seeing is the form and in the Details portion of it is the subform that you see a little of at the bottom. What's puzzling to me is that now when I choose a value in the dropdown for the Customer ID, it isn't populating any of the fields. Pretty sure this was working before and leaves me wondering about some wonderful Windows update that hit. However, further down the form I've got a button that I click and it passes the info in this form on to a report I've made that displays the info. Through that button I actually have it load the report, save the report as a PDF and then close the report. So, when I click the button to generate that PDF, I go to where it has saved the file, open it up and all the data is there as it should be. So my question is, does anyone have any idea as to why the form would suddenly decide to not populate the data in the form. Clearly from the end report it is still working. Both form and report, as well as subform and subreport, work off the same queries.

[pbaldy]

What's the code behind the combo? Did you perhaps rename the combo, which would have disassociated the code?

[June7]

Is main form DataEntry property set to yes?

[Waterdog]

June7, yes it was set to yes. Which wasn't a problem before. I've now tried to set that to no and it's still doing this.

pbaldy, there's not really any coding behind the combo box. The Customer ID is a field on a table called tblReturns. And that field is tied to the primary key CustomerID on a customer table. So you choose the ID and it should populate the rest of the fields. I'll double check naming but I don't recall changing anything there.

Here's the only coding of sorts behind it:

SELECT [tblCustomer].[Customer ID], [tblCustomer].[Company] FROM tblCustomer ORDER BY [Customer ID];

[pbaldy]

Without code (or a macro), selecting a value from the combo isn't going to populate anything else. Typically one of two things is coded:

• go to the record selected in the combo
• populate related fields in the form from the table the selections in the combo come from

The first can be done via the combo wizard. This is a way to do the second:

BaldyWeb - Autofill

[June7]

Unless you want to use Access intrinsic search tools, a control used to input/select filter criteria must be UNBOUND, otherwise changes data in record. If this combobox is for the purpose of finding an existing record, it must be UNBOUND with code behind it to do the find.

If the combobox is used for data entry, then bind it to field and use the method described by Paul to display related info.

[Waterdog]

OK, so here's the basic idea to this. When opening the form, the first field to deal with should be CustomerID. The user can click on the box to select an existing record, or just type in the number to bring up that record. At the same time I want to have the ability to type in a number and if that ID doesn't exist allow for adding that customer into the database.

Just tested again, adding a new ID into the mix, seemed to work just fine. However, if I were to try and type in info for an existing ID, then it gives me an error about duplicate values. Again, ultimately when I click that Save button that I've put on the form, it creates the PDF of the report that displays the info and all that works great. It's like there's some setting that I've accidentally switched perhaps in the table to cause this?

[pbaldy]

If it's a "find existing record" combo, it shouldn't be bound and it sounds like it is. Try using the combo box wizard as I recommended, choosing the third option.

[Waterdog]

OK, so here's the properties on this guy. From how I read these settings, I should be able to edit fields and such just fine. Limit To List is set to No.

[June7]

Is the main form bound to Customers table?

As stated, use an UNBOUND combobox to select customer so as to find existing customer record. If the customer does not exist, then use code to allow creating new record. Use the combobox NotInList event. Code would move to New Record row to allow entry of new customer data.

[Waterdog]

The main form pulls data from multiple tables through the use of a query.

[June7]

Usually a form can do data entry/edit for only one table.

So what table is the main form intended for data entry/edit? Data from other tables is for information display, can't edit those related records.

If it is not the Customers table, then use a BOUND combobox to select customer and its NotInList event to allow adding new customer in Customers table 'on-the-fly' during data entry.

[Waterdog]

Well, the main table associated with it is called tblReturn. This table contains the CustomerID which is linked to the CustomerID field in the tblCustomer table. The return table also contains info like tracking number, quantity of boxes, date received, etc. And then the form also has the customer name and address fields on it which should be populated by choosing that Customer ID.

From my attempts it looks like adding a new customer works just fine. It's the selecting an existing customer that it's tripping up on.

[June7]

You want to enter a new record in tblReturn - move to NewRecord row and select customer from combobox. Combobox bound to CustomerID field in tblReturn. The CustomerID value will be saved into tblReturn record. So make sure the combobox ControlSource is set to the correct field.

There really is no need to include any other tables in the form's RecordSource. In fact, if the query uses INNER JOIN, that will prevent entering new records.

Use UNBOUND textboxes with expressions that refer to columns of combobox to display the related customer info.


Is this a Split form? Is that how you get the datasheet at the bottom?