String Concatenation - where did I go wrong?

[Lol Owen]

Hi all, hope someone can point out the schoolboy error I'm probably making here

I have a button on a form, formA, when I click this button it opens a new form, form B. Data entered on form B is displayed as a summary on form A in continuous sheet style. Form B has a button it so that when I've finished entering my information, clicking the button will write the record, requery the Form A, and move the cursor to the last record, i.e. the one just created in form B, to give visual confirmation. Thing is, I have 6 different versions of formA, which form B can service. However, for the requery and goto last record to function I need to be specific on the name of the form. A typical form name is frmBookT101 where T101 is field Room Number. The field Room Number appears on form B, so I've tried to concatenate a string from "frmBook" and the value of field Room Number displayed on form B, as below:


Private Sub Make_Booking_Click()
On Error GoTo Err_Make_Booking_Click
Dim strName As String

strName = "frmBook" & Me![Room Number]

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms![strName].Requery
DoCmd.GoToRecord acDataForm, "strName", acLast

Exit_Make_Booking_Click:
Exit Sub

Err_Make_Booking_Click:
MsgBox Err.Description
Resume Exit_Make_Booking_Click

End Sub


On form B field Room Number is selected from a combo box, if this is relevant.
I keep getting an error message saying that the form"strName" cannot be found.

Where am i going wrong?

Thanks, Lol

 

[modest]

You should do two things.


The first, which is just good practice is:

strName = "frmBook" & Me![Room Number]
should be changed to:
strName = "frmBook" & Me.[Room Number].[Value]

The dot operator has intellisense and you don't need to use the bang operator here.




Second, which should remove the error by itself:

DoCmd.GoToRecord acDataForm, "strName", acLast
should be changed to:
DoCmd.GoToRecord acDataForm, strName, acLast

The quotes mean you're actually looking for the form called "strName" instead of "frmBook<RoomNumber>"


hope this helps
-modest

 

[Lol Owen]

Thanks modest, that works fine, except the requery function is not working. I've tried various combinations of the DoCmd method but am getting nowhere.

Any ideas please?

Lol

 

[Rich]

You should do two things.


The first, which is just good practice is:

strName = "frmBook" & Me![Room Number]
should be changed to:
strName = "frmBook" & Me.[Room Number].[Value]

The dot operator has intellisense and you don't need to use the bang operator here.





hope this helps
-modest

A couple of problems here Modest, you should actually rename the control to something other than the field name, Room Number would become txtRmNumber or some such, and the .Value part isn't required.
For intellisense to then work properly it would become
Me.txtRmNumber

also note that the DoMenu item although still used by the wizards is obsolete,
replace it with
DoCmd.RunCommand acCmdSaveRecord

 

[Lol Owen]

I've found the DoCmd method isn't appropriate in this case.

Using the Forms[formname].requery will not work when a string is substiututed for the formname. i found the Case-Select method as below works just fine

Private Sub Make_Booking_Click()
On Error GoTo Err_Make_Booking_Click
Dim strName As String
Dim strUpdate As String
strName = "frmBook" & Me.[Room Number].Value
strUpdate = Me.[Room Number].Value

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Select Case [strUpdate]
Case "T101"
Forms![frmBookT101].Requery
Case "T102"
Forms![frmBookT102].Requery
Case "T103"
Forms![frmBookT103].Requery
Case "T104"
Forms![frmBookT104].Requery
Case "T105"
Forms![frmBookT105].Requery
Case "T106"
Forms![frmBookT106].Requery
End Select

Thanks for your help folks, it's much apprecciated

Lol

 

[MarkK]

You might also reference a member of the forms collection as follows:
Forms("frmBook" & strUpdate)
This one liner eliminates the need for your Select Case statement:
Forms("frmBook" & strUpdate).Requery

 

[dt01pqt]

You should do two things.


The first, which is just good practice is:

strName = "frmBook" & Me![Room Number]
should be changed to:
strName = "frmBook" & Me.[Room Number].[Value]
-modest

Your right on this one modest and I disaggre with rich. Yes it will work if you don't have the .value but it is still better sense. The reason being is a control is an object not a variable (also not all controls are obvious). You don't need to put the .value in brackets because it is not a field but that's just a matter of preferece.

 

[Lol Owen]

Thanks lagbolt I'll try that one in another dbase (I'm letting well alone now it works!).

WRT the .value I found that it would only work with the .value in place.

Thanks again, and see my new post for more cries for help!

Cheers, Lol

 

[modest]

and see my new post for more cries for help!

Lord knows I have my share of requests

 

[WayneRyan]

The .Value is the default for MS Access. If you don't specify it, that's what
you get.

However, that might not be true in subsequent releases, and it is certainly
not the default for other MS products like VB.NET

Wayne

 

[Lol Owen]

The problem I have is I'm doing a HE course in Business IT, the lecturers take the F.O.F.O. approach to learning so I'm scrabbling around with whatever books I can lay my hands on and resources such as this (which is brill). Consequently, all my Access is self taught

Thanks for your help, Lol

 

[Rich]

Your right on this one modest and I disaggre with rich. Yes it will work if you don't have the .value but it is still better sense. The reason being is a control is an object not a variable (also not all controls are obvious). You don't need to put the .value in brackets because it is not a field but that's just a matter of preferece.

I see, so you wouldn't re-name a control to say txtRmNumber then?

 

[modest]

I see, so you wouldn't re-name a control to say txtRmNumber then?

Why do you keep wanting to do that?

You don't need to and it would be easier to understand for Lol if you just used the fields name. Thoreau had one of the best quotes in the world, "Simplicity! Simplicity! Simplicity!" Keep things simple

-modest

 

[Mile-O]

Why do you keep wanting to do that?

You don't need to and it would be easier to understand for Lol if you just used the fields name.

To disambiguate the difference between a field on a form and a control bound to the field.

 

[Mile-O]

And, to Lol Owen, why do you have so many forms with similar names?

 

[RoyVidar]

Yup - disambiguate - not only for your own confusion, but also to prevent Access being confused. If you keep the control names the same as the controlsource, as is the usual way of the Access Wizards, you may end up wondering why a simple =sum(SomeFieldWithTheSameNameAsAControl) gives you #Name in stead of some figures.

 

[Mile-O]

Bear in mind to use prefixes when working with tables and queries too - I've seen so many people make a query with the same name as their table and then try to count it - but which are you actually counting?

 

[modest]

Yes, I understand, I'm saying in his case just keep it simple until he's ready to learn more: basics first. Don't teach someone how to build a car before you teach them how to build an engine.

 

[Mile-O]

Yes, I understand, I'm saying in his case just keep it simple until he's ready to learn more: basics first. Don't teach someone how to build a car before you teach them how to build an engine.

I've always subscribed to the tenet of not encouraging bad habits/practice from the start; otherwise they'll be building the same old engine time after time. And a Model T's engine won't go down well in Forumula 1 today.

 

[modest]

Haha this thread is getting off topic. But it's not bad practice to do it the way it was. Everything was clearly defined and specifically referenced correctly, Access would have no problem in understanding which object came from which form.