VBA Open Record to Form (1 Viewer)

Juett

Registered User.
Local time
Today, 12:07
Joined
Jul 16, 2019
Messages
71
[SOLVED] VBA Open Record to Form

Hi there...I have an issue I'm struggling with...hoping someone can help.

I have a Query that outputs a simple list of records in Datasheet view, and I have an embedded macro that opens an individual record from that datasheet in a Form when you click on it....pretty simple stuff.

The embedded macro passes the value from the field Doc ID to the form's record source field of the same name to ensure that the record clicked is the same one that opens in the form. The form displays the record in edit mode.

The Doc ID field is captured from a table record that is a primary key field, with formatting added to the front (e.g. record 1 displays as WPS001 etc using the format "WPS"000.)


The embedded macro works fine using the where condition: ="[Doc ID]=" & "" & [Doc ID] & ""

The problem...I have tried to replicate the embedded macro in VBA...this is where I get stuck. I have tried multiple variations and suggestions from other sites, and all I can get the form to do is open on a blank record.

For example, I have tried:

DoCmd.OpenForm "FrmCustomerExisting", _
WhereCondition:="[Doc ID]=" & "" & [Doc ID] & ""

DoCmd.OpenForm "FrmCustomerExisting", _
WhereCondition:="[Doc ID]=' " & "" & [Doc ID] & "" '"

DoCmd.OpenForm "FrmCustomerExisting", _
WhereCondition:="[Doc ID]=" & Me.[Doc ID]

And many other attempts. Nothing works. Any suggestions would be very much appreciated.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:07
Joined
Oct 29, 2018
Messages
21,357
Hi. From just looking at it, I would say the third version you posted above should have worked. One good thing about using VBA over a macro is you can step through the VBA code as it executes and examine all the information to find out if you're getting what you're expecting to be there in the code.
 

Juett

Registered User.
Local time
Today, 12:07
Joined
Jul 16, 2019
Messages
71
I agree...but as I said..it opens the right form, but to a blank record. I am totally perplexed!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:07
Joined
Oct 29, 2018
Messages
21,357
I agree...but as I said..it opens the right form, but to a blank record. I am totally perplexed!
Hi. So, have you tried stepping through the code?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:07
Joined
Sep 21, 2011
Messages
14,038
Where is this code? as Me will refer to a form/report?
 

KlausObd

New member
Local time
Today, 13:07
Joined
Jul 19, 2019
Messages
2
Hi,

how to write a compare in VBA generally depends on what field-type the value is. If it's a

number - then you just write "[Doc ID]=" & Me![Doc ID]

number with decimals: [Doc_Number] = str(Me!Doc_Number)

string - then you say: "[DOC_String] = '" & Me![MyStringField] & "'"

Date+Time - ist more complicated, you have to form a term like
[YourDate] = #yyyy-mm-dd hh:mm:ss AM/PM#

Just Date [YourDate] = #yyyy-mm-dd#

For date + Datetime i use the following vba-functions:

Function SQLDatum(Datumx) As String
'Macht aus irgendeinem gültigen Datum einen String #yyyy-mm-dd# (ISO-Norm Datum)
If IsDate(Datumx) Then
SQLDatum = Format(CDate(Datumx), "\#yyyy\-mm\-dd\#", vbMonday, vbFirstFourDays)
Else
SQLDatum = ""
End If
End Function


Function DateTimeForSQL(dteDate) As String
'Datum incl. Uhrzeit für SQL und INI-Files als String

' DateTimeForSQL = Format(CDate(dteDate), "\#yyyy\-mm\-dd h:nn:ss AM/PM \#", vbMonday, vbFirstFourDays)
DateTimeForSQL = Format(CDate(dteDate), "\#yyyy\-mm\-dd hh:nn:ss\#", vbMonday, vbFirstFourDays)

End Function

and then just

Date+Time - [YourDate] = DateTimeForSQL(Me!Datetimefield])

Just Date - [YourDate] = SQLDatum(Me![Datetimefield])



And i personally always open the form without named parameter like

DoCmd.OpenForm "frmName", acNormal, , [Your Where Clause]

and the Where-clause comes after the THIRD comma ...

Maybe that helps ?

mfg Klaus
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,169
if you are opening the form frmCustomerExisting from elsewhere other than the original form, you must qualify the where clause:
Code:
DoCmd.OpenForm "FrmCustomerExisting", _
WhereCondition:="[Doc ID]=" & [COLOR="Blue"]Forms!yourOrigFormName![/COLOR][Doc ID]
 

Juett

Registered User.
Local time
Today, 12:07
Joined
Jul 16, 2019
Messages
71
So..turns out the form I was trying to open had the Data Entry Property set to YES...switching it to NO solved the issue (go to Form Properties - Data - Data Entry).

This then enabled the following code to work perfectly:

DoCmd.OpenForm "FrmCustomerExisting", , , WhereCondition:="[Doc ID]=" & Me![Doc ID]

Can't believe I missed this! Thanks everyone for your help and suggestions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:07
Joined
Oct 29, 2018
Messages
21,357
So..turns out the form I was trying to open had the Data Entry Property set to YES...switching it to NO solved the issue (go to Form Properties - Data - Data Entry).

This then enabled the following code to work perfectly:

DoCmd.OpenForm "FrmCustomerExisting", , , WhereCondition:="[Doc ID]=" & Me![Doc ID]

Can't believe I missed this! Thanks everyone for your help and suggestions.

Ah, of course. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom