Trouble with Access Query and Form

[Djblois]

I have created a query that will query from an access table that I created and our main Oracle tables. When I first created it I had my users input all the orders and the customers and storers attached to the orders into my system. However, since they are already inputting that information into our Main Oracle database. I thought it would be more efficient to pull the customer name and storer name from Oracle. Less redundancy. All they are supposed to do now is input the order number that is attached to the appointment and it is supposed to read the customer and storer from the oracle table. It is supposed to save the order number from the oracle table and put it in my table so it would be attached to the appointment. I think this is where I am having the problem.

However, It is reading the data for any orders previously inputted but it won't allow my users to attach any new orders to appointments. It will allow them to type it in but once they exit the orders box the whole line dissapears therefore not saving it. I have the combobox set to look up all the order numbers we have in our main Oracle Database. (I even trried to change it to a textbox) (Also, they should not be able to change the numbers in the oracle table) I set it with this row source:

SELECT [WMS_PKO_HD].[REF] FROM [WMS_PKO_HD] ORDER BY [REF];

then I set the control source to save that order # that they chose to a table (Orders_Table) that I created in Access and the field is Order# . This is how they attach orders to appointments in my program.

Here is the SQL for the query that the form is attached to:

Code:
SELECT Orders_Table.ord_ID, Orders_Table.[Order#], WMS_PKO_HD.SHIPTO, WMS_PKO_HD.STORER, tblStatus.Status
FROM (Orders_Table INNER JOIN WMS_PKO_HD ON Orders_Table.[Order#] = WMS_PKO_HD.REF) INNER JOIN tblStatus ON WMS_PKO_HD.FLAG = tblStatus.Code
ORDER BY Orders_Table.ord_ID;
when a user choses one of the order #'s it doesn't even fill the other controls with the information.

[OBP]

Dan, Hello again. I am not too familiar with working with Oracle but I can ask some Questions.
When you had the Combo for Order # did it display them OK?
Was the Combo Bound to the Orders_Table Order # Field?
Did it put the Order # in that Field?
Did you try VBA to in the Combo's After Update event procedure to put the Order # in tot an Unbound text box, to see if the order # is actually being "Selected"?
You are doing this on a Form, based on your Access Orders Table or Appointments Table?

[Djblois]

I am actually not having any trouble with the Oracle table - it is reading from it correctly. Now to your questions:

When you had the Combo for Order # did it display them OK?

Yes it displayed correctly pulling all the Order numbers from the Oracle table field [REF]

Was the Combo Bound to the Orders_Table Order # Field?

I don't understand what you mean. The Control Source for the Combo was Orders_Table.[Order#] field

and the row source was the WMS_PKO_HD.[REF] field

Remember, I want it to read the order number from WMS_PKO_HD.[REF] and then save it to Orders_Table.[Order#]. It should not save, edit or delete from WMS_PKO_HD.[REF]

Did it put the Order # in that Field?

The Order # would show in the Combobox after the user typed it in or picked it from the drop down but then when they exit the combo the whole line dissapears. Also, after you typed it in Customer and Storer are not showing up in the other fields. This makes me think that the problem is it is not storing to my table.

Did you try VBA to in the Combo's After Update event procedure to put the Order # in tot an Unbound text box, to see if the order # is actually being "Selected"?

It will not work because the minute you exit the field it dissapears.

You are doing this on a Form, based on your Access Orders Table or Appointments Table?

It is a form based on my Orders table. Each appointment may have multiple Orders attached to it.

[OBP]

Dan, form your description it is the Combo's Control Source that is the problem, "The Control Source for the Combo was Orders_Table.[Order#] field".
Are you sure that the Field is "Updateable", you haven't made it an Autonumber by mistake, Also is the actual Recordset for the Form Updateable?
Is it a Query, can you add an order # in it manually?
Also in the Form manually?

[Djblois]

"The Control Source for the Combo was Orders_Table.[Order#] field".Are you sure that the Field is "Updateable", you haven't made it an Autonumber by mistake

No it is not an AutoNumber. My users are using a previous version of the program attached to that same field and it is allowing them to enter data and save it.

Also is the actual Recordset for the Form Updateable?

I do have allow edits, allow additions, and allow deletes all set to true.

Is it a Query, can you add an order # in it manually?

Yes it is attached to a query. And I can manually add an order# to that queary

Also in the Form manually?

No, whenever I add it manually or through the combobox in the form and exit the field it, then the whole line disappears.

[OBP]

Have you checked that it doesn't go in the table?
i.e. it is moving on to a New record when the old record disappears?
You haven't got the Form set to data Entry = " yes"?

Without seeing the database it is very hard to come up with what is wrong.
Does the Form recordset type property say Dynaset?

[OBP]

Also check that the form Cycle Property is set to Current Record.

[Djblois]

Also check that the form Cycle Property is set to Current Record.

This helped a little bit. Now it will not dissapear after exiting the Combobox. But the Appt id field goes blank so it is not saving it to the appointment like I thought. I think it is just reading from the Oracle table but not saving to the access table. It actually will not even let me move to another line. and the delete button will not work either, the only way to get ride of it is to hit the escape key.

[OBP]

I would recreate the Form based on the query but without the combo and check that it updates manually. Then try adding the combo back in.

[hansup]

This helped a little bit. Now it will not dissapear after exiting the Combobox. But the Appt id field goes blank so it is not saving it to the appointment like I thought. I think it is just reading from the Oracle table but not saving to the access table. It actually will not even let me move to another line. and the delete button will not work either, the only way to get ride of it is to hit the escape key.

As OBP mentioned, without seeing the database it is very hard to come up with what is wrong.

Can you make of copy of your database, re-create your Oracle WMS_PKO_HD table a s a native Jet (Access) table, and populate the tables with just enough bogus (non-sensitive) sample data so we can see how your form is interacting with the table? If that's reasonable, make a Zip archive file from it and upload the Zip here.

Hans

[OBP]

Hansup

I will have to go soon, can you take over for tonight?

[hansup]

Hansup

I will have to go soon, can you take over for tonight?

I'll give it my best effort, but forms aren't my strong suit.

I admire your generosity and patience.

Regards,
Hans

[Djblois]

If I inputed a table from Oracle into Access would it delete the table from Oracle or just create a copy?

[OBP]

Have you got Relationships set up between the Access and Oracle tables?
Is that preventing data entry?

[OBP]

I would use a Make Table query.

[Djblois]

Have you got Relationships set up between the Access and Oracle tables?
Is that preventing data entry?

I do not have relationships set up. I have set up an inner join in the query.

I would use a Make Table query.

I am trying to give you a copy to look at now. How do I do this?

[hansup]

If I inputed a table from Oracle into Access would it delete the table from Oracle or just create a copy?

In the copy of your database, delete the link to your Oracle table. Then chose "File -> Get External Data -> Import" from Access' menu to create a Jet copy of the Oracle table.

If it doesn't work for you as I described, give me some details about how you're linking from Access to Oracle. We can probably figure out an alternative.

Hans

[hansup]

I would use a Make Table query.

Good! There's more than one way to skin this cat. Worst case, he could just create an Access table from scratch.

Hans

[Djblois]

Here you guys go - Have fun. There is two sections. A front end and a back end. I imported the Oracle tables into the front end.

[Djblois]

I would need to email the front end - even after zipping it, it is still over 1 mb. Can one of you PM me your email.