Field value from multiple records VBA (1 Viewer)

fat controller · Sep 23, 2015

Is there a way to have VBA code return the data values from the same field on more than one record?

Essentially, I have a system whereby a vehicle is allocated a number (RunNumber), and for the most part the vehicle will remain allocated to that number from the start of the day to the end, however there are times where it will be de-allocated (handled by a radio button), and then allocated another number - there is the potential for this to happen three or maybe four times in extreme circumstances. Each allocation of a run is a separate record.

Each vehicle has a unique ID of its own, and I already have a query that will return all the records for the specified date range and show all Run Numbers that vehicle had been allocated to - however, I am wanting to incorporate the answer with a sequence of other checks in a bit of VBA, and have the answer to all those checks (variables) returned on a form.

If a vehicle has been allocated to run 10, 192 and 56 during the course of the day, I am looking for that information to be displayed to the user, something along the lines of:

"Today, this vehicle has been allocated to runs: 10, 192, 56" - what would I need to do with code to get that sort of return?

pbaldy · Sep 23, 2015

Something like this?

http://allenbrowne.com/func-concat.html

fat controller · Sep 23, 2015

Once again, you come to my aid

You are a true gentleman, thank you.

pbaldy · Sep 23, 2015

Happy to help!

fat controller · Sep 23, 2015

OK, I am struggling a little with this - I have created the module and saved it, and the module appears to be fine (I have stepped through the code, and it is going to the module and following it through without issue), but I am clearly getting something wrong with the code that uses the function as I am getting an error message

Error 3061: Too few parameters, Expected 1

The code I am using as it stands:

Code:

Dim BonnetNumber
Dim WhatRuns

BonnetNumber = Me.txtBonnet.Value
WhatRuns = ConcatRelated("RunningNumber", "VehicleCheck", "[BonnetNumber]=""" & BonnetNumber & """")

Me.txtRuns.Value = WhatRuns

The field RunningNumber is a text field (rarely more than 3 characters)
"VehicleCheck" is a query that has a Where condition which selects records based on the date contained in a field on the form
The field BonnetNumber is a text field (rarely more than 6 characters)

I suspect that the problem might be something to do with the quotation marks in the ConcatRelated line, but am not overly sure?

pbaldy · Sep 23, 2015

No, I think the error comes from trying to open a recordset on a query with a form reference in the criteria. You can test by taking the criteria out. Presuming so, your simplest workaround is probably to wrap the form reference in the Eval() function:

Eval('Forms!FormName.Textboxname')

Cronk · Sep 24, 2015

Or try, if BonnetNumber is a numeric field, leaving off the enclosing quotes
eg
WhatRuns = ConcatRelated("RunningNumber", "VehicleCheck", "[BonnetNumber]=" & Me.BonnetNumber )

fat controller · Sep 24, 2015

Thank you both

I am absolutely slammed now for the next two days (Rugby World Cup duties call!), so will be coming back to this most likely on Sunday or Monday. I didn't want you to think I had gotten an answer and disappeared.

I will post back Sunday or Monday

pbaldy · Sep 24, 2015

No problem. I think Cronk missed:

fat controller said:
The field BonnetNumber is a text field (rarely more than 6 characters)

fat controller · Sep 29, 2015

Sorry for the delay in coming back to this

OK, I have changed the code to:

Code:

Dim BonnetNumber
Dim WhatRuns
 
BonnetNumber = Eval(Forms!VehicleCheck.txtBonnet)
WhatRuns = ConcatRelated("RunningNumber", "VehicleCheck", "[BonnetNumber]='" & 
BonnetNumber & "'")
 
Me.txtRuns.Value = WhatRuns

And I now get a different error:

Run-time error '2482':

Microsoft Access cannot find the name 'SLE60' you entered in the expression.

the SLE60 is correct, as that is the very thing I want it to look for, but clearly something is wrong?

arnelgp · Sep 29, 2015

on your VehicleCheck query, did you include RunningNumber and BonnetNumber on your returned column?

fat controller · Sep 29, 2015

Yes, the query returns both of those, and the query has criteria as follows:

ServiceDate = [Forms]![VehicleCheck]![txtServiceDate]

BonnetNumber = [Forms]![VehicleCheck]![txtBonnet]

The query itself runs fine, so all I really want to do is to be able to show the information returned from the query on the form.

What about making the form a continuous form, then opening it with filters? Can I filter on two criteria when opening the form?

The form being opened does not have the same recordsource if that makes any difference?

gemma-the-husky · Sep 29, 2015

not sure what the concatrelated function does,

I would do this by extract a query with the runs in which you are interested, (in this case you get a query with 3 rows)

then iterating the query rows to concatenate the result

sort of

Code:

 while not end of recordset
      result = result & " this route details"
      next record
 wend

I looked now, and it's the same idea. The concat function in this case is possibly a bit overkill for what you are trying to do and I am sure you could simplify it for your use,, but you must just have a field name wrong somewhere.

fat controller · Sep 29, 2015

Sorry, I wandered off down a wee path there without looking back...

I have switched the form to a continuous form and now it has the same record source as the main/parent form that it is opened from; I have had a wee bit more success, but still not quite there.

So far, I have got as far as:

Code:

Dim BonnetNumber
Dim ServiceDate
BonnetNumber = Me.BonnetNumber.Value
ServiceDate = Format(Me.ServiceDate, "dd\/mm\/yyyy")
DoCmd.OpenForm "VehicleCheck", , , "ServiceDate=#" & ServiceDate & "#" And "BonnetNumber='" & BonnetNumber & "'"

Now, if I open the form with only the ServiceDate part of the where condition, it opens; and if I open it with the BonnetNumber part of the where condition it opens; ask it to do both and it falls over with a Syntax Error

I feel that I am so close to achieving what I need, it almost hurts!

arnelgp · Sep 29, 2015

you have error in concatenation in the where clause should be:

DoCmd.OpenForm "VehicleCheck", , , "ServiceDate=#" & ServiceDate & "# And BonnetNumber='" & BonnetNumber & "'"

fat controller · Sep 29, 2015

Blimey, definitely a case of not seeing the wood for the trees there! Thank you arnelgp, that works beautifully now; all I need to do now is work the rest of my little tasks out, and I will be sorted.....

fat controller · Sep 30, 2015

OK, I am back at this once more, as I now need to have all the values from one field on a table show in the same unbound textbox (txtTypes)

The form's On Open event has (amongst other bits of code):

Code:

Dim Types

Types = ConcatRelated("VehicleTypes", "TypesKnown", "EmployeeNumber='" & [txtEmployeeNumber] & "'")

Me.txtTypes.Value = Types

Now, this time I am getting a completely different error than I was the last time I tried using this function, a screenshot of which I have attached. I have been ultra-careful to get the field and table names correct in the code, and have double checked them.

Am I wrong to be using the quotation marks?

Or, is there another (preferably simpler) way to show the contents of the same field, from all records matching a criteria?

pbaldy · Sep 30, 2015

The lack of a space after SELECT would have me double checking the function.

fat controller · Sep 30, 2015

Good call - there was a space missing after SELECT, so that is now back in place and I am back to square one - - Too few parameters, expected 1

Is there any other way to show this sort of result?

pbaldy · Sep 30, 2015

Can you attach the db here?

Field value from multiple records VBA (1 Viewer)

Slightly round the bend..

Wino Moderator

Slightly round the bend..

Wino Moderator

Slightly round the bend..

Wino Moderator

Registered User.

Slightly round the bend..

Wino Moderator

Slightly round the bend..

..forever waiting... waiting for jellybean!

Slightly round the bend..

Super Moderator

Slightly round the bend..

..forever waiting... waiting for jellybean!

Slightly round the bend..

Slightly round the bend..

Attachments

Wino Moderator

Slightly round the bend..

Wino Moderator

Similar threads

Users who are viewing this thread