Still can't solve a 1-to-1 relationship problem

[czarjosh]

I posted a thread about a month ago, and the simplest answer seems to be at the bottom of the thread(

http://www.tek-tips.com/viewthread.cfm?SQID=840461&SPID=700&newpid=700&page=1)

I still cannot get it to work though. I type in the code as posted and when i click on teh combo box i get an "Enter Parameter Value" dialgue box with this displayed "Forms!RegForm!RecNumber" I am not sure why it is doing this because, I copy and pasted the text. Changed my RegistrationID field to RecNumber and of course made the formnamefield to the name of my form, which is RegForm.

Any thoughts on resolving this 1-to-1 relationship problem.

 

[lespaul]

post the code and SQL you're actually using please.

thanks,
leslie

 

[czarjosh]

This is the ROW SOURCE for the Combo Box:
SELECT A.[last Name], A.RecNumber FROM Registration AS A WHERE [A.RecNumber]<>Forms!RegForm!RecNumber ORDER BY A.[last Name];

This is the Code for the After_Update:

Private Sub cboRegistrants_AfterUpdate()
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Registration", dbOpenDynaset)
rs.FindFirst "[RecNumber] = " & Me![cboRegistrants]
If Not rs.NoMatch Then
rs.edit
rs("Roommate") = Me![cboRegistrants]
rs.Update
Else
MsgBox "No Match Found -- However Impossible!!!"
End If
rs.Close
db.Close
End Sub

 

[lespaul]

Is the combo box on Forms!RegForm!RecNumber?



Leslie

 

[jebry]

Hi!

Is RegForm open? and is the text box actually named RecNumber? Finally is RecNumber also the name of the field?



Jeff Bridgham
bridgham@purdue.edu

 

[czarjosh]

The Combo Box is unbound. The RegForm is open, because that is the form the combo box is on. I do not have a text box named RecNumber, it is just a field name.

 

[PHV]

WHERE [A.RecNumber]<>Forms!RegForm!RecNumber
In RegForm you should have a control named RecNumber containing the value you don't want in your combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[czarjosh]

so do I add a text box or something with that expressionm? I do not quite understand how to impliment your suggestion.

 

[LittleSmudge]

Which column is the bound column in cboRegistrants ?

Because if it is col 1 ( as default ) then

rs.FindFirst "[RecNumber] = " & Me![cboRegistrants]

is trying to match a number with a name

Try using
rs.FindFirst "RecNumber = " & cboRegistrants.Column(1)







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

I do not quite understand how to impliment your suggestion
Do you know why you have such where condition in your sql ?
I've made NO suggestion, I just said that you get the parameter dialog because the lack of RecNumber control in RegForm.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

SELECT A.[last Name], A.RecNumber FROM Registration AS A WHERE [A.RecNumber]<>Forms!RegForm!RecNumber ORDER BY A.[last Name];

If this query is the source of the combo box, this is saying:

when you populate this combo box with choices, go select the LastName and RecNumber from the table Registration where the field RecNumber(In the table) has the same value as the value in the form RegForm in the component RecNumber. Do you have a component named RecNumber on RegForm? If the component RecNumber on RegForm does not have a value you will get prompted to enter the parameter.

Leslie

 

[PHV]

where the field RecNumber(In the table) has NOT the same value as the value in the form RegForm in the component RecNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[czarjosh]

ok i figured out how to record the roommate in the combo box and in the table. I just sat up the lookup field in the table and voila it worked. Now i have 2 more questions.

1) HOWEVER i want it to be if i go to the josh record and assign ian as his roommate

when I go to ian JOSH SHOULD already be his roomie

2) Once a Roomie is assigned they no longer appear in the list.

 

[lespaul]

what is the table structure for recording roommates?


Leslie