Help !! runtime error trying to retrieve record

[k_c_morrissey]

I have an 'add new order' form which opens with all the fields blank and focus set to a combo box. The first thing the user must do is select a contract name or number from an unbound combo box. I then want the form to update and show all the relevant information in the form fields.

In the row source of the combo box I select all 3 fields from the same table (Project_Info). Only the contract number is visible all others are set to 0cm width in combo box properties.

In the afterupdate event I have the following code to set the field to the values from the select query

My code looks like this:

Private Sub ProjectContractNum_AfterUpdate()
Dim sOrder_idSource As String
Dim rs As Object
ProjectName = ProjectContractNum.Column(1)
proj_status = ProjectContractNum.Column(2)

End Sub
[end code]

My SQL in rowSource looks like this:
SELECT Project_Info.proj_name, Project_Info.proj_contract_no, Project_Info.proj_status FROM Project_Info; 

Access tells me that I don't have a join key in my record set.
message reads as follows:-
Run-time error '2147352567 (80020009)':
Cannot add record(s);Join key of table Project_Info' not in record set.

Debug highlights this line:
   proj_status = ProjectContractNum.Column(2)

'proj_status' is the name of the text box and the control source.


If I just use just the first 2 fields (project number and project name) these two work!!

What am I doing wrong?

Thx, Kev.

 

[Len Boorman]

Since you have the contract data in the combo box then you must have this basic data already within a table, Why then do you want to effectively add it again albeit to perhaps another table.

Data duplication is a nightmare and leads to incosistency within the database.

Think I would ask myself why I am adding this data when really I only beed the PK of the contract .

Len B

 

[k_c_morrissey]

Len,

I'm pretty new to this DB stuff (you probably guessed!)

I don't actually want to add any data at this point, I just want to look up the contract name, number and status. This info is in a table called project_info.

The two combo boxes are unbound, but linked by some VB code that updates one from the other by using the value of the unseen column when it is selected. (If you select project by project number you only see the number in the combo but after selection the other combo box displays the name, and vice versa.)

What I really want to achieve is to have the user select the project and then have the rest of the project info update on the form automatically (the project status is just one of about 10 other fields that I need to update.

So I could test this I add the status in the same way as I did with the combos, using hidden columns on the lookup and a bit of VB to update the fields with an afterupdate event.

Each of the combos selects the data in an SQl from rowsource and is bound to all of the columns (in this case 3) with all but the one I want to see in the combo set to 0 width.

I hope I am explaining this OK.

Thx,
Kev

 

[Len Boorman]

Basically then why not have an Enquiries Form, Basic form with no actual source. Add a combo box with Project References as you already have.

Use the on click property to Open another form which is based upon a query that calls all of the project info you want the user to see and the project field in the query has a parameter that is the contents of the combo box.

The Results form is based upon the query and thus you have all the Project info displayed.

If you are really slick you have a Button which Says Project Information. The on click property goes something like
if cbo_project.visible=false then

cbo_project.visible=true
cbo_project.setfocus
cbo_project.dropdown
else
cbo_project.visible=false
end if


(cbo_project visible property set to no)

The on click property of the combo box has an event
docmd.openform "frm_Results",,,acformreadonly
docmd.maximise.

On the results form have a button which has an on click event

docmd.close acform,"frm_Enquiries"
docmd.openform "frm_Enquiries",acnormal,,,acformedit
docmd.maximise
docmd.close acform,"frm_Results"


Basically this is a looping type thing.
Click the button
Project combo opens
Select Project
Results Form opens
Click Close button and Enquiries form closes and re opens (combo box now invisible again
Results form closes

This way you can set up a bunch of enquiries on the one form and use combo boxes as the parameter within queries supporting results forms

Any help ?.

len

The results form can be used to further drill down if so desired