Double click

[Burden]

Ok here's a good one.

I have four tables called:- Main Control (Main Form Data), Board
Information (Details about Board Revision), Software Information
(Software relating to the Board Revision) and SF Numbers (a table of
incrementing numbers for ref to the software)

On the Main Control subform i have the fields at the top from the Main
Control Table, then two subforms from the Board and software tables.
All are linked great and you can scroll and all the records change
according to the main Design Number from the Main table.

On the software subform at the bottom i have a field called SF number.
I would like to be able to double click this field and it to go to the
SF numbers table get the last record and plus 1 then store it in both
SF numbers table and in the software information table in the correct
field.

This is so that the same number is never enetered and also for the
user not have to look around for what the last number issued was.

Also what links do i have to create for this?

Possible?

 

[Arvin Meyer [MVP]]

Air code:

Sub MyField_DblClick()
If Len(Me.MyField & vbNullString) = 0 Then
MyField = DMax("MyField", "MyTable) + 1
End If
End Sub

 

[Burden]

That worked great thank you.

So glad that there are people like you out there who know this sort of
thing as i am learning Access now and i wouldn't get very far without
these posts.

Burden

 

[Arvin Meyer [MVP]]

That worked great thank you.

So glad that there are people like you out there who know this sort of
thing as i am learning Access now and i wouldn't get very far without
these posts.

Fifteen years ago when I was learning Access, Microsoft had free phone
support. I made intensive use of it. This is one of the ways I am paying
that debt back.

 

[Burden]

Hi again,
Just trying to use this database and have noticed that the formula
above is only half working. It is getting the last number and adding 1
then storing it in the software information table when the record is
saved but not saving that new number in the SF numbers table.

Therefore every time i double click the field it gives my the same
number as nothing has changed in the SF numbers table.

i.e. Last number = 120, double click and it gives me 121, double click
the next record and it gives me 121 again

Any ideas?

 

[Arvin Meyer [MVP]]

Hi again,
Just trying to use this database and have noticed that the formula
above is only half working. It is getting the last number and adding 1
then storing it in the software information table when the record is
saved but not saving that new number in the SF numbers table.

Therefore every time i double click the field it gives my the same
number as nothing has changed in the SF numbers table.

i.e. Last number = 120, double click and it gives me 121, double click
the next record and it gives me 121 again

Any ideas?

Yes, you need to explicitly save the record between double clicks. Are you
sure you want to do that at this point? If so, just add a line of code like:

Sub MyField_DblClick()
If Len(Me.MyField & vbNullString) = 0 Then
MyField = DMax("MyField", "MyTable) + 1
End If

DoCmd.RunCommand acCmdSaveRecord

End Sub

If it's not what you want to do, you can create a static form level counter
and add double-clicks. More complex, but doable.

 

[Burden]

I understand that you would have to save the record, but what i am
saying is that even after it is saved the new SF number is not being
stored in the SF Numbers table where it has looked and added one, only
in the software information table.

Burden

 

[Burden]

Does any one have a answer for my problem?

I can not deploy this database until this problem is solved.

 

[Gina Whipp]

Burden,

If I understand what you are asking for then it sounds like you need to
UPDATE and addtional table. Try adding this in the Sub MyField_DblClick()
after the SaveRecord line:

DoCmd.RunSQL "UPDATE YourTable SET YourTable.YourField =
FieldOnYourFormWhereValueIsStored;"