Still having problems wiht my query/form

clauses

Registered User.
Local time
Today, 23:51
Joined
Feb 9, 2001
Messages
56
I have a form that uses a query as the record source. This query is a parameterized select query that uses two tables. Table no 1 is used to display order information and can be written to or updated; the form is used for entering/updating orders. Table no 2 is used to obtain the customer name and address for informational purposes and is not updated.

The form that uses this query contains an unbound combobox in its header that allows the user to select customers from the customer file. Afer a customer has been selected the customers ID is moved to a bound textbox; that is used as the queries parameter, and a requery action is perfromed to allow the user to see all the orders the selected customer has made or enter a new order for that customer.

When ever the requery action is perfromed a new record is created using the default values for all the key fields except for the customer number, which contains the value selected by the user. Unless I delete this record from the file I am unable use the form again for this customer because I receive an attempt to write a duplicate record error.

Is there a property I can set or a programming technique I can use to keep the new record from being created.
 
If you are just using the customer ID to filter the recordset, don't move it to a bound field, this is probably where your duplication error is creeping in. Instead use the unbound combo value to filter your query in the same way that you are using the textbox.

Judging by your data structure, make sure that you have a main form / subform display with the customer details on the main form and the order details on a subform. Each order should have it's own ID but linked to the Customer Table via the CustomerID. I may be preaching to the converted but it never harms to revisit your structure:)

HTH
 
I had a feeling that might be my problem but after making the change I am having some problems getting the form to perfrom the requery and display my information. The criteria on my form is structured as follows:

forms!frmOrders!cboCustomer.Text

This structure seems to work when I call the query directly but I have the combo box set up to perfrom a requery when there is a change and nothing seems to happen. No records are found and there are no errors.
 
Set the criteria to just

Forms!frmOrders!cboCustomer

and on the After_Update of the combo set the event procedure to

Me.Requery

Make sure that the value you want to query on is the first column in the combo. If it is not, refer to the column you want to use in the criteria. ie if the PO number is in the 2nd column, your criteria would be

Forms!frmOrders!cboCustomer.column(1)

HTH
 
Changed the criteria as you suggested and also changed the event to perfrom a requery with the structure you suggested but I still have no results. When I ran it through debug the requery action is perfromed without error but I still have no records to look at. With my test data I should have two records for the customer I am selecting.
 
I suspect it has something to do with how the combo is being referenced and also the column the data is in. What order is the data in that supplies the combo (from the QBE design grid)?
 

Users who are viewing this thread

Back
Top Bottom