Data missing

[Paul Cooke]

I am sure I am missing something very simple here but for the life of me can't seem to think what is is !!

this is just a basic example here for simmplicity!

I have a two tables

Tbl1
NameID (PK)
FirstName
LastName

&

Tbl2
EventTypeID (PK)
Date
NameID

NameID is related on a 1 to many relationship from Tbl1 to Tbl2.


On my Tbl2 Form I have various fields inclding a Combo box from NameID. If I drop down the box the values are there to select no problem (so control source is fine), But when I select one and finish completing the form the value selected does not appear in the table or on any reports.

I really have no idea why not!

Can any one please help me !!

many thanks

 

[John Big Booty]

Is the Combo bound to the Field NameID? Does the bound column of the Combo contain the NameID?

 

[CBrighton]

I suspect you are talking about a main/sub form split here, that the NameID (fk) field in the table bound to the subform is not being populated automatically with the NameID from the record in the main form?

Subform controls have properties to store which fields the main and subforms are linked. Ensure the appropriate fields have been input into these properties for both the main and sub form.

 

[Paul Cooke]

Thanks for the replies

I have just checked the form and the control is unbound? I have selected the dropdown from control source and run the report but it is giving me a number 1 in field of the report.

Im assuming it is a query I need but what one?

Many thanks guys

 

[John Big Booty]

You will probably need to use ComboName.Column(#) to return the desired data in your Report rather than the data held in Column Zero of your combo.

Replace # with the column number that holds that data, remembering that columns are numbered from zero up.

 

[Paul Cooke]

Thanks John but I think I may not be explaining myself well (as usual sorry)

for clarification I will try to explain in detail with an example..

I have 1 table called 'Details' with
DetailsID (Pk)
Names (Text)

I have 1 table called 'New' with
NewID (PK)
Reason (Text)
Other (Text)
DetailsID (FK, Number)

I now have a form called 'NewContact'

On this form I have all the controls from the table 'New' and changed the control 'DetailsID' to a combo box (which says it is unbound) I have the row source set as

SELECT Details.DetailsID, Details.Names FROM Details;

(the Control source is empty)
The column count is 2 and column widths are 0cm and 4cm.


When I go into the form to enter data the combo box lets me choose items in the dropdown from the 'Details' table.

but when I go into the 'New' table the 'DetailsID' is completely empty even though I selected a value from the combo on the form.

what I need is for whatever value I choose from the Combobox on the form to populate into the 'New' table.

does that make more sense?

thanks again for your help

 

[John Big Booty]

OK now I'm really confused are we dealing with a report (your post #4, referred to a report) or a form

I suspect that you will need to select DetailsID (in table New) as the Control Source for your Combo, once you have done that you should find that selections made in the combo are stored in that field.

 

[Paul Cooke]

sorry about the repoort confusion - I don't make things easy !! The report mentioned was really for me to highlight the fact that the values entered were not going into the table and the report was based on the table contents.

as to setting the Control Source to DetailsID in the table New I don't think I can do that as it is the Foriegn Key and has to be a Number type

is that right?

thanks again

 

[John Big Booty]

The foreign key type should match the primary key type that it is storing.

 

[Paul Cooke]

I think I will leave this one for now John as it is becoming very confusing to me quiet simply as I don't understand whats is going on with this DB. I am going to 'stand back' from it for a while as it is giving me brain damage !!

Thanks for your patience tho

 

[John Big Booty]

Can you post a copy of your DB ('03 format for preference, certainly no newer than '07), and I'll have a look at it.

 

[boblarson]

as to setting the Control Source to DetailsID in the table New I don't think I can do that as it is the Foriegn Key and has to be a Number type

is that right?

thanks again

JBB has told you what you need to do. Your combo row source tells the combo what values it can provide. As long as you have the ID and then the Name in the row source query (which your example shows) then as long as you bind the combo to the field DetailsID then it will correctly store the foreign key and it will DISPLAY the name. But when you go into the table the ID will show which is correct.

When you want to do up a report based on that information you create a QUERY (Don't use just the table) for the report which will include BOTH TABLES and linked on your PK/FK. Put the name field from the lookup table into the report and it will display the name.

I hope that maybe helps clarify it.

 

[Paul Cooke]

Many thanks Bob and John - I think I was having 'Database Overload' as I came back to it this morning saw your post and John's and everything was clear again !! I must stop over commplicating things is my new mantra for this week !!

Thanks again guys