access table field question

[simplerequest]

In a simple access table, would like for FDL1and FDL 2 to auto generate

fld one is facility number (5digit)
fld two is the facility name

Table contains: field name, field number, date received, number of, number without, selection 1, option 1, selection2, option 2 ...selection 3, option3

When I can receive info with or without the facility number.......so when entering the row, I would like to be able to use the facility name from the combo box and the field number will automatically appear in the next field.

This will help to verify the correct number is assigned.

This table will intimately be used in a sub form
Help please I cannot find anyone to help figure this out. I was able to get this to work on a from, but that was a mistake to make changes on the form, because the table do not work now

Novice trying to keep her job thanks in advance

[RuralGuy]

What value is actually recorded in the table? All that is needed is a ForeignKey from the facility table. The a query can join the two tables so you can see whatever fields you need from either table.

[simplerequest]

FACNUM FACNAME RecDate Method ENTRYDATE NumCases NUMWITHtx OHYLTR NR Total Cases Site 1 Frequency1 Site 2 Frequency2 Site 3 Frequency3 Site 4 Frequency4 Notes

[RuralGuy]

You are using a ComboBox to populate a field in this table. What is the SQL for the RowSource of the ComboBox and what are the BoundColumn and ControlSource values?

[simplerequest]

You are using a ComboBox to populate a field in this table. What is the SQL for the RowSource of the ComboBox and what are the BoundColumn and ControlSource values?


Yes I am using a ComboBox to populate the FacNum field. The SQL ( according to the access drop down is ) SELECT [surgery Services].FACNUM FROM [surgery Services];

TheRowSource is

The BoundColumn


The ControlSource value is






























For the FACName field the sQL statement is SELECT [surgery Services].Field2FROM [surgeryservices];



http://bytes.com/



unable to get screen shot

[RuralGuy]

Maybe if you posted your db? Remove any sensitive data of course.

[simplerequest]

no problem how and where should i do that

[RuralGuy]

Compact and Repair your db first then zip it up. Then reply using the Post Reply button. If you scroll down you will see a Manage Attachments button. Push it and upload your db. Then Submit Reply.

[simplerequest]

rural guy am trying to do as suggested please bear with me i am in a panic

[simplerequest]

here is the database

[RuralGuy]

There might be a minimum post count before attachments are allowed. You can send it to Rural Guy at Wild Blue dot Net if you want.

[simplerequest]

ok
will do in a few

[simplerequest]

it is done how about your end?

[RuralGuy]

Looks like it made it. You should really Compact and Repair first and then Zip it up to save transmission time. I'll have a look at it.

[RuralGuy]

What form are you working with here?

[simplerequest]

oh you got it really

[RuralGuy]

...and what table(s) are we trying to update? It looks like this data has been imported from an Excel file. True?

[simplerequest]

the working rec reports form perfoorms the function I am trying to get
when the combo box is used and a facility number selected the name box fill with the correct facility name

[simplerequest]

some of the table were from an excell file

i am unable to get the form to fill the table because I made correction to the form and not on the original table rec reports

there are a few different version of the form and table becuase if i deleted the tables or forms , the form would no longer work

[simplerequest]

in the forms area the working rec report form if viewed from the design view and select the datasheet view the forms works perfectly but you cannot save the form in the table view

[RuralGuy]

Here's a link you need to read: The Evils of Lookup Fields in Tables

[RuralGuy]

Have you done any reading on Normalization of tables? There are a number of issues with your structure that will continue to cause you grief until the tables are normalized.

[RuralGuy]

@simplerequest
What does this line mean? What is the Table view?

[RuralGuy]

Did you get my last email?

[simplerequest]

look in the form table working rec reports selecting the design view drop down and select the datsheet view

a table will appear this datasheet view appears to be a table but it is not.

[simplerequest]

email on yahoo?
going

[simplerequest]

no RG I have never read anything on normalization
i just quickly read the Evils of Look Up Fields in Tables

what i read makes me think that I am really way out in left field

is it after 3 am where you are? where are you and do you ever sleep?

[RuralGuy]

@simplerequest
This is the Datasheet view of the form and can be made the default if you want. You can not see the command button on your form in that view.

[RuralGuy]

It is only 9:30 PM here right now and I do sleep...often ;). What you have can be fixed but you really need to do some reorganizing first. How much time do you have on the project? Days or weeks perhaps?

[RuralGuy]

We can pick it up again tomorrow if you want.

[simplerequest]

Rural Guy are you still there?

[RuralGuy]

Yes indeedy. May I be of assistance?

[simplerequest]

Short on time i agrre i need to reorganize it i was afriad to delete tables
are you able to see my vision ?

I really thought this would be simple , well not simple but not complex in nature

what do you think?

[RuralGuy]

@simplerequest
I'm sorry but not really. You seem to be trying to fill out the RecReports table but I have no idea what is needed in there. The important thing here is there is no reason to duplicate data in a RDBMS. Keys are duplicated in the tables but the actual data only resides in one table.

[simplerequest]

It is after 11pm. Is there a way to get the table to work, without using the combo boxes

[RuralGuy]

The ComboBoxes can be on the form, just not in the table. I'll play a bit but I really do not understand the table.

[RuralGuy]

Is the [cAmbulatory Services Surgery and] table where the [FACNUM] and [FACNAME] fields are home base? Is this the main table for this data? Who assigns the FACNUM?

[simplerequest]

You seem to be trying to fill out the RecReports table but I have no idea what is needed in there.

Answer:
The RecReports=received reports. In this table I will enter the date I received the reports, how many I recieved, how many were submitted with treatment, how many are non reportable, the promary site and how many of each primary site were received.

(sorry that file did not have the complete design view page descriptions )


The info in RMCDS is the respones to a checklist. The information was entered in this table as I received the returned checklist.

I hope to enter the checklist into into a TAB on the NameForm. as a subform.
Another TAB on the name form would be the rec reports table.

[simplerequest]

yes that is the source

[simplerequest]

Rural Guy i just read your comment "This is the Datasheet view of the form and can be made the default if you want. You can not see the command button on your form in that view. "

Does the default then make it a table? Where I can enter and save the info?

Once I get the table to perform, I will just make a quick form, and use the form for data entry.

[RuralGuy]

Give me just a minute and I'll send you an email.

[RuralGuy]

email on the way. Let me know what you think.

[simplerequest]

cannot open zipped file

[RuralGuy]

What OS do you use? It should be on a right click of WinXP.

[RuralGuy]

Sent it again unzipped.

[RuralGuy]

BTW here's a pretty good breakdown for Normalization on this forum.

[simplerequest]

reviewing

i see the change you made on the form with the combo box

but i dont see how this works for the table there is not facility number on the table i will need that

[RuralGuy]

You have the FacID in the table. With that you can always get the facility number with a join. That is the beauty and the power of queries and joins. It is as if all of the related tables are just one big table.

[RuralGuy]

Open the qryRecReport directly. Don't you see the values you need in there?

[simplerequest]

falling asleep will read the normalization tomrrow. can we meet again tomorrow?