Calling Code from a Module

[Perfac]

Ignore my last post.

If I place the expression =DMax("JointObjectNoA","q1JointObjects")+1 in one of the text fields and name it JointTableNextNo, it gives the correct number.
One line in your code in Module 1.


frm.Controls(sQueryField) = DMax("[" & sQueryField & "]", "[" & sQueryName & "]") + 1

What is the correct way to replace the red part; Me.JointTableNextNo?

[Perfac]

I learnt as we continued, because of your advice.
Now I think this is the easiest, and a way that will save the most time. This code below work on the form. The blue is the text fields on the form.

If Me. ThisTableCount > 0 Then
GoTo Skip
Else
If Me.ThisTableCount = 0 Then
Me!RecordClassNoA = Me.JointTableNextNo
Me!FienamIDa = "JOO" & Me.JointTableNextNo
Me!FieldNameNoA = Me.ThisTableNextNo
End If
End If
Skip:

The three in green are fields in the source, still the same as before.
How should the module and "call" look now.
Sorry for taking up so much of your time but thank you.

[Perfac]

The three text fields are populated this way.

ThisTableCount = DCount("*","q6FieldNames")
JointTableNextNo = DMax("JointObjectNoA","q1JointObjects")+1
ThisTableNextNo = DMax("FieldNameNoA","t6FieldNames")+1

[Perfac]

Latest sample

[Gicu]

I still think a lookup table would be easier to implement, as you would need to change the control source for the text boxes (and add them to each form). Please look at the new table t6FormSettings, I added 3 records for three forms and added the code to the three forms (the call it is much simpler, just pass Me as the argument).

Cheers,

[Perfac]

Hi. 24h00 here. Already slept 6 hours, ate too much on Father's Day, so 14 hour "shift" starts now. Feeling sharp.
I am sure this will work. I thank you very much.

[Perfac]

1. Previously my app had one joint table t1CombinedEntities. There are three joint tables now.
2. t1JointObjects, t1RecordsClass and t1RecordsMoney.
3. Large tables like PAYE Tax Rates, Postal Codes and Vehicle Values that get updated every month are not joint to any other object.
4. This code you are helping me with comes on every form that are joint to one of the three "Joint Tables" in 2. above.
5. You and I mostly discussed around form f6FieldNames as example, which link and combine with t1RecordsClass.
6. The result of this code must be that when a new record is added in f6FieldNames at least the 6 fields below must be populated.
a. Query q6FieldNames field FienamIDa Short textfield A number like REC589, if it is the 589th record.
b. Query q6FieldNames field FieldNameA Short text.
c. Query q6FieldNames field FieldNameNoA Number. Maybe 187. The next record in f6FieldNames.

d. Query q1RecordsClass field RecclaIDa Short Text. Same Number as in 6a. REC589
e. Query q1RecordsClass field EntityNameB Short Text Field. The same string in 6b must come in this field.
f. Query q1RecordsClass field RecordClassNoA Number. It must be 589.

The sample you returned to me does not do exactly that now. But I believe you helped me to get it done.
I will let you know.

[Perfac]

Last message gone??
Great everything work on the sample you returned. EntityNameB is populated by a line that should not be in your code.
Thanks a million.

[Gicu]

You're very welcome, please let me know if you find any kinks >

EntityNameB is populated by a line that should not be in your code.

Not sure what you mean with this, but seems like you have it under control.
Cheers,