Access 2003 help on building a form with both static and dynamic data

wallis34,

You can create a table in Access that has the same key field as the Oracle table.
Then link it to the Oracle table in a query.

Then create a form from this query.

Then you should be able to update the needed data directly.

JeffCoachman

So rather than having a report like the attachment, I would want to make it a form? If so, is there a way to like port over the report into a form, keeping the formatting and etc?  Thanks!

First make the query and see if it works

You can copy the report controls to a form by doing this
Open the report in Design View
Click: Edit-->Select All

Open a new, blank form in design View.
Click: Edit-->Paste

Now it might not be perfect, and you will still have the Control Sources and the RecordSource to deal with.

Thats cool, i can try that. I have the query. What i did was created a make table query first, ran that and then set the primary key on Part_ID and the made table seems to link with Oracle tables. What i am most unsure of is how to build the form in a way that the fields are dynamic. what is the control source and record source syntax?

OK i copied over the report fields, they seem all broke. I think i need to somehow group by Work_Order_ID as on the report. How do i do this in Form? I have attached a screen shot looking at both the form and report field property sheet. Let me know if you would  like to see any further info to better help my sorry butt. TIA Bill
BacklogForm1.jpg
Backlogreport1.jpg

Sorry, I got the Objects mixed up.
For some reason I thought you were copying a report to another report.


First things first.
Did you build the query?
Does it function the way you want?

built the query, it seems OK, When u say function the way i want it too,,, it returns what i need, and the fields that need to be linked to the form have no data, so as far a s the query it is good.

But can you update the data as well?

Using a update query? i know how to do that, that is not what i want to do. Here is except from first post:  "What we would like is a form or report where we can manually add the start finish times and other text, and have it save this data in a dynamic table linked by order number and part_ID to look just like the screen shot so all the boxes on the right will be dynamic. TIA  Bill"   Sorry but this seems frustrating, i appreciate your help, this is complicated i know.

You say:
"Using a update query? i know how to do that, that is not what i want to do."
OK, so you do not want to update anything, great.

Then you say:
"manually add the start finish times and other text"
Well, adding start and finish times and "Other text" IS Updating data?

Can you clarify?

the update queries i have done updated tables manually by selecting new data from excel spreadsheet to update fields.

how does a form like above, put data in a table?

wallis34,

I am still not understanding.

Here is a sample.

It simulates what I belive is your situation.
tblOracleProject, represents the Oracle table.
tblProjectDates, represents your Access table.

They are linked in the database window as One to One

They are linked in the query named qryProjectInfo.

Now, ignoring the Form/Report, is this a valid representation of your data structures?

JeffCoachman

Access-EEQ24181244UpdateLinkedOn.mdb

Yes it is, see attached a screen shot of the linking structure, LSI user input table is the table i made with fields for user input.
Query1.jpg

So, based on my sample what are you needing to do?

how to set the Control Sources and the RecordSource , that allows users to input text in the fields of the LSI_User_table. It looks like the form has lost all the links that the report had. Is there a way to view the report and have the fields updatable?

Does the table SysAdmin_Part have an Autonumbering Field? (ID)

No it does not.

Then as I see it, in order for this One-To-One system to work, one table has to be the "Lead" table.
This is the table that will create the Primary Key Value and populate that value into the other table.

In your situation, what is the "Lead Table"?

JeffCoachman

Ahh yes, i have already done this, i think it works. i attached the SQL. I don't know how to get the form to layout like the report, allowing the correct fields to be dynamic. I also attached a screen shot with linked table LSI_User_Input, this is the table with the fields that i want to be able to dynamically update.
SQLBacklogQuery.txt
QueryBacklogReport.jpg

Just try making a form from this query.

If you "Creata a record, does it generate the coresponding Key Value in the other table?
And can you create the coresponding record in the "Other" Table?

I do not know how to do this, can you note the steps?

Don't know how to create a from?
Or don't know how to create a new record?

I can create a new form in design view. What are the next steps? TIA, Bill

If you create a new record in the "Lead" table, does it create the matching record in the other table?