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 :thumb

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

[hansup]

Hansup :thumb

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.:bow:

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.

[hansup]

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.
Sorry, Daniel, I didn't even think about database format. But I only have Access 2003, so I can't open your database. Assuming Access 2007 allows you to convert the database to an earlier format, you might try 2002-2003 or 2000 formats. I just don't know about 2007 features.

Once more, I'm sorry I didn't anticipate this stumbling block.

Hans

[hansup]

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.I think you can click on members' names to send them email. I'll give you my address anyway:
hans.updyke@gmail.com

Please see my previous message about the database format. I can't do anything with Access 2007.

Hans

[Djblois]

I am converting it to 2003 now. I would still need your email to send you the front end as it is larger than 1 mb

[hansup]

I am converting it to 2003 now. I would still need your email to send you the front end as it is larger than 1 mbNo worries. Click on hansup to the left of this reply. Among the available choices in an option to send me an email.

Actually, I'll do that now for Djblois to send you an email. You will have my address then.

Hans

[Djblois]

Here is the back end in 2003 format.

[hansup]

I have an update for anyone following this thread. When working with the forms in the Access 2003 version of Daniel's front-end database, Access 2003 repeatedly crashes on me. Rebooting Windows did not fix the problem.

I created a new blank database and successfully imported the tables, queries, and standard modules. However, when I tried to import the forms, Access repeatedly crashed again. I also tried go the other way --- opening Daniel's database and exporting the forms to my new database. But the crashing continued.

So for now I am stuck. If anyone can suggest a method to avoid the crashing, please let me know. Maybe someone with Access 2007 would have better luck with the 2007 version of his front-end.

But that's all I got for now.

Hans

[Djblois]

I did not know the forms are so different. I thank you for all your help hansup.

[OBP]

Dan, do you still need help or did Hans fix it?
If so can you send me your Front End, I have pm'd you my email address.

[hansup]

Dan, do you still need help or did Hans fix it?
Hello OBP,

I tried, but couldn't accomplish anything. :dunno

Good luck,
Hans

[Djblois]

OBP,

do you have access 2007? because if you dont it will probably crash on your system as well.

[OBP]

No, I would like to try the 2003 version for myself.

[hansup]

do you have access 2007? because if you dont it will probably crash on your system as well.Good morning, Daniel.

I should clarify that issue. I could open forms in design view and I could view code in the form modules. But I'm not proficient enough to understand how all the pieces fit together without actually running the form. Access crashed when I switched from design view to form view, either immediately or in very short order. My best guess at the cause is that your forms incorporate 2007 features unavailable in Access 2003. But that's only a guess.

Hans

[Djblois]

I think you are right Hansup but I do not know what the differences are to try to remove them for you.

[OBP]

Which Form(s)?

[Djblois]

frmOrders and subfrmOrders. They both should work pretty much the same. The only difference is subfrmOrders opens within the frmOpenDetails and the frmOrders opens as a popup to the right of the frmScheduled_Appts. They are both set up for users to enter orders attached to an appointment and to view them.

[OBP]

I have emailed you a new version of the database it has a working form called qryOrders.
You do not need the "Storer" field in the Orders table as it is in the "WMS_PKO_HD" table, unless they are different.
I don't know if you want the "Status" showing on the form, it can be shown in the Combo.
I have included the Data database in the zip as well as you will need to re-establish the Links.

[OBP]

Dan, how is that form supposed to connected to the "Appointment"?