Unable to edit current record (1 Viewer)

[CJBIRKIN]

Hi

I am trying to use a combobox to create 2 entries is a table. The first entry is simply the selected item from the combobox, the second is created as follows

Private Sub CMBO_OPERATOR1_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

    Me.Bookmark = rst.Bookmark



If Me.CMBO_OPERATOR1 = 1 Then
    rst.Edit
        rst![OPERATOR_ID_FK2] = 3

            Else
                rst.Edit
                    rst![OPERATOR_ID_FK2] = 4
End If

rst.Update
Set rst = Nothing

Me.TXT_OPERATOR2.Requery
End Sub

My problem is a catch 22. The record doesn't exist to be cloned until i complete certain data items one of which is the one being created here, and for the same reason i cannot force a save first as i get error messages saying the data item is missing.

Is there anyway to do this?


Cheers

Chris

 

[Fizzio]

Rather than on the Combo AfterUpdate, use the code in the Form_AfterUpdate ie the record will be saved and the record will exist to be cloned (I think!)

 

[CJBIRKIN]

Hi Fizz

At what point does the form afterupdate event fire?

I am wondering if the problem is that i have field validation in the tables and this is in some way preventing the data from being created until all of the fields are complete??

If you look at the pic you can see what i'm trying to do. Basically i am trying to create a staff rota system for our anaesthetics department. I need a rules form that can allow the user to create recurring leave like "I'm away the first wednesday of everymonth as i teach at ....." This will then be translated into a specific period of non-availabilty. I have an operator table that has 4 records.

After,Before,Start,End

If the user selects After then they will only be able to select Start

as in i will be away the 1st monday After the Start of everymonth...


or before = end

as in i will be away the 1st monday before the end of everymonth...

I being a bit dumb as i can calculate from the first entry what the second entry will be and just display it in a text box, then when i do the queries i can do the same. The point is i would rather have the data in the table.

Any ideas??

Cheers

Chris

 

[Fizzio]

At what point does the form afterupdate event fire?

When the last field is exited, before moving to a new record.

I am wondering if the problem is that i have field validation in the tables and this is in some way preventing the data from being created until all of the fields are complete??

You no doubt have the Required flag set to yes for that field, stopping the save.

The point is i would rather have the data in the table.

Why, when you have already identified that you can calculate it!

 

[CJBIRKIN]

Hi

Where to begin!

I'm so pissed off now that i want to get this to work even though the calculation method would work,face, nose, spite, cut off!.

There was one field that was set to Yes so i removed that.

I unbound the textbox and on the afterupdate event of the combobox filled the textbox in depending on the selection.

Works so far.

On the afterupdate form event i then coded as before spotting the deliberate mistake.

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

    Me.Bookmark = rst.Bookmark

should be

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

    
[B]rst.Bookmark = Me.Bookmark [/B]

However an error is triggered before the beforeupdate and afterupdate form events! it says a related record is required in TBL_OPERATOR. This is the "lookup" table that shows the description of the operator. So basically it's saying i need to complete the field before i can complete the field!

So my question now is, should lookup tables be defined in the relationships and if so should the have referential integrity enforced? I am assuming that this is the cause of the error.

I'm feel like i'm going round in circles. I can't complete a record until it's created but i can't create a record until it's complete.





Any ideas



Cheers

Chris

 

[Fizzio]

Whoa, you are getting into Pat Hartman territory now - I'll answer the question but I expect to be corrected

should lookup tables be defined in the relationships

Not necessaarily so as you can define a join in any query that you may produce. But if the ID of the lookup field is ever to change then yes, I think they should be joined at table level.

Try taking out the join in the relationships window and see if it works (but test thouroughly)

 

[CJBIRKIN]

Hi

Thanks for all your help Fizz.

I've come up against this sort of problem before and resorted to removing the table from the relationship. Which worked by the way!

It just doesn't seem logical/right though, i always think that i must of made a mistake somewhere and i'm working around the problem rather than fixing it a source.

Any way thanks again

Chris

 

[Fizzio]

No problem. I agree, it seems to go against the normal ethos of aspiring to the nth Normal Form but if you look at it from another angle, the relationship is not vital to be defined within the relationship window.
**Braces for a Hartman Backlash **