No field referance?

[Adrian510]

I added a field to a table and then went to reference it in a standard module and its not there? i.e. pressed ctrl + space and didn't appear in the list so i tried referencing it anyway and no joy.

I feel sort of stupid posting about it but after many hours of checking and comparing to all the other fields (which work) I can't see whats wrong.

I have tried to search the forum but no joy, is it a standard rookie query / mistake?

Regards

Adrian

 

[RuralGuy]

Are you referencing the table or a query in your standard module? You must go in to each query that references the table and add the field. Access does not do this for you.

 

[Adrian510]

I am refering to the table. e.g. Field = DayStart in tblProductionDetails.

I then want to go into the module and insert

DayStart = NextValueCounter

I have done this for other fields in the table without a problem just by going into the module ctrl + space to find the field and then inserting the relevant bit of code but this time this new field does not seem to be there?

Any help appreciated.

Regards

Adrian

 

[Bat17]

Is this in a form module? Have you added the field to the form?

Peter

 

[RuralGuy]

Would you post your procedure code so we can look at it?

 

[Adrian510]

Hi RG and Peter

As you can see Peter I have taken your advice over the tab contolled form and its working much better. I overcame the original problem by copying the previous record and I am going to set the relevant values back to null as the situation demands.

I am also aware that my DB is not properly normalised yet and my storage of the code i.e. not in different modules which i call leaves a lot to be desired but as I am sure you have gathered this is still a learning experience for me.

I have attached a copy of the DB rather than just the code as it makes more sense and commented what i am trying to do in the VBE.

The underlying idea is to have a unique number attached to all records that are recorded via the form from the time the cmdDayStart is clicked.

Regards

Adrian

 

[Adrian510]

Just read the last sentance again and it is not very clear. To clarify each record recorded on that particular shift (which are not set times and can run over multiple days) has to have the same unique number attached to each record tha is created during that shift. So when i go to run a report i can group by unique shift number.

Hope this helps and somebody can see what is going wrong and can point me in the right direction

Regards

Adrian

 

[Bat17]

If I understand you right then try this.

Function GetNextAvailableCounter()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblCounterTable")
rst.MoveFirst
GetNextAvailableCounter = rst!NextAvailableCounter
rst.Edit
    rst!NextAvailableCounter = rst!NextAvailableCounter + 1
rst.Update
Set rst = Nothing
End Function
Private Sub cmdDayStart_Click()
Me.txtubiqasedcacac = GetNextAvailableCounter
InstanceStart = Now
mpgLine.SetFocus
End Sub

I would also have another button on the Day End tab to give them a chance to go back if they hit the wrong option.

Peter

 

[Adrian510]

Thanks for the response Peter

I am sure your code is right but i have not had a chance to try it yet.

The problem i was having was that i was unable to reference UniqueShiftCounter in the VBE.

But i have been looking into it further and after pulling the field from the table into the form and then deleting it all off a sudden I can reference it. I don't know why this worked which really bugs me but it does and at this stage (deadline approaching) I am just going to accept that it did.

Thanks for the help to date.

Adrian