How can I utilise the showfinddialogue method to return a value to a location / waypoint which cant be resolved from my access recordset?

The syntax for the ShowFindDialog is different from the objFindResults, note the address city, and state are enclosed in quotes, so you will have to convert your address into one string.
"One Microsoft Way, Redmond WA"

Once you have the pushpin obj you can get its location and make it a way point if thats what you need etc...

Dim objpp As Pushpin
Set objpp = MappointControl1.ActiveMap.ShowFindDialog("One Microsoft Way, Redmond WA", geoFindAddress)
MsgBox "The pushpin name is: " + objpp.Name