Go to record after a selection - Sort of

[indesisiv]

I will try and explain the situation:

1. I have a main form that the user inputs details .. all works fine
2. The user clicks a button to add / edit / view (depending on the selection) details in a linked form.
3. Viewing the details is fine. There is a box at the top that is populated fron the main form and then it uses a selection in a combo box to bring up the details .. this all works fine


4. The problem is when i come to add the details in.
There is a duel primary key made up of UnitName & Service
Unit name is autoselected so that it refers to the main form. The service is in a combo box that the user selects. BUT!!! Onlyafter you try and update the record it tells you that you have a duplicate primary key.... What it NEEDS to do is ... when the service is selected from the combo box if there is already a record in then it needs to be shown on the screen so that it can be edited. If there is not a record that maches then the user can just carry on and make one.


Hope that all makes sense.
Any help will be gratefully recieved
Steve

 

[FoFa]

When the users makes the selection, use Dcount to return a count of the Key, you will get either 0 or 1 (assuming duplicates are not allowed). If 1, display the record, if 0 add a record.

 

[indesisiv]

i can do the dcount bit but my problems is how to make it go to the record

Steve

 

[FoFa]

What I usually do (because I am lazy) is use the wizard to create a combobox that does the lookup/goto. Then I take the wizard generated code and wrap my code around it. I find this works pretty good for the most part.

 

[indesisiv]

I can't seem to get the wizard to create the code for a lookup using a combo box.

I am sure that the problem is because the combo box is bound.

HELP!!!

Steve

 

[indesisiv]

It kinda works

I have it sort of working ...
it recognises that it has found the matching record but then i get an error .. see bottom

the basic code i am using is

Private Sub cboServiceSelect_AfterUpdate()
Dim unit
Dim service
unit = Me.txtNameOfUnit
service = Me.services
'these are in in case i have to change the names of the text boxes


Dim rs As Recordset
Dim db As Database
Dim criteria As String


Set db = CurrentDb()
Set rs = Me.RecordsetClone
criteria = "[name of unit] ='" & unit & "' AND [services] ='" & service & "'"
rs.FindFirst criteria
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Me.Refresh
Else
MsgBox "Record Not Found"
Me.Undo
Me.txtNameOfUnit = Me.lblUnitName
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

the only problem is that i get an error
runtime error 2105
You can't go to the specified record ..
You may be at the end of a recordset

Any ideas???

 

[FoFa]

Well, for what reasons could it not move to that record? Record Locked? Record does not exist? Current record is held (won't allow you to move off). You can't use a bound Combobox I don't think, because it would try to change the underlaying row to what ever you selected. So, some thing you are doing is causing this, you just have to figure out what it is. You just have to think it through a step at a time.

 

[indesisiv]

Thanks

Cheers for that FoFa.

The problem was that the combo was bound.
Have replaced it with an unbound one set to make the other one invisible.

Works fine now.

Steve