Macro to VB

  • Thread starter Thread starter Chappie
  • Start date Start date
C

Chappie

In a simple macro, I can use the following syntax in a
where condition to open a new form with the current record
displayed:

[DLCDGrant#]=[Forms]![frmGrantSumDE].[GrantSumID#]

However, when I include this language as a where condition
in the DoCmd.OpenForm action of VB, the correct record is
not opened. How do I change the syntax to make this
work? Do I need to declare a variable? If so, what would
the syntax of the variable be?

Thanks for any help.

Chappie
 
In the Where condition, try
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#]
if GrantSumId is numeric
or
"[DLCDGrant#]= '" & [Forms]![frmGrantSumDE].[GrantSumID#] & "'"
if it is text

Hope This Helps
Gerald Stanley MCSD
 
Gerald,
Your suggestion worked perfectly - now I have two follow-
ups.

1) Can you explain the use of ",' and & in your
examples. I know that double quotes define a string,
ampersand is concantenate and a single quote is a comment,
but that doesn't explain how they work together. Why
different for numeric versus text fields? I know this is
basic stuff, but it just doesn't make sense to me. A good
FAQ (which I haven't found) would be great too.

2) I intended to use the docmd as a part of an If
command. If there is a matching record in the second
table, use the docmd
((DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#])) to open the form with the current record.
Else open a message box to ask if the user wants to create
a new record. I am having trouble defining the condition
in the If command. Can you assist?

Thanks again,

Chappie
-----Original Message-----
In the Where condition, try
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#]
if GrantSumId is numeric
or
"[DLCDGrant#]= '" & [Forms]![frmGrantSumDE].[GrantSumID#] & "'"
if it is text

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
In a simple macro, I can use the following syntax in a
where condition to open a new form with the current record
displayed:

[DLCDGrant#]=[Forms]![frmGrantSumDE].[GrantSumID#]

However, when I include this language as a where condition
in the DoCmd.OpenForm action of VB, the correct record is
not opened. How do I change the syntax to make this
work? Do I need to declare a variable? If so, what would
the syntax of the variable be?

Thanks for any help.

Chappie
.
Click to expand...
.
Click to expand...
 
1) You are composing a string for the WHERE condition.
Your original statement was being translated into looking
for row(s) where the column [DLCDGrant#] was equal to the
string literal [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the double quotes and the & makes the
translation [DLCDGrant#] being equal to the value held in
the identifier [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the single quote is a JET requirement to
differentiate strings from numbers (and from dates, which
need to be enclosed in # marks) in comparisons.

2) You should be able to use the DLookUp function and
check for a Null Return indicating no match e.g.

If IsNull(DLookUp("[DLCDGrant#]", "YourTableName",
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#])) Then
MsgBox "No Matching Record"
Else
DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#]
Click to expand...
End If

Be sure to insert your own table name as the 2nd parameter
in the DLookUp statement.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald,
Your suggestion worked perfectly - now I have two follow-
ups.

1) Can you explain the use of ",' and & in your
examples. I know that double quotes define a string,
ampersand is concantenate and a single quote is a comment,
but that doesn't explain how they work together. Why
different for numeric versus text fields? I know this is
basic stuff, but it just doesn't make sense to me. A good
FAQ (which I haven't found) would be great too.

2) I intended to use the docmd as a part of an If
command. If there is a matching record in the second
table, use the docmd
((DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#])) to open the form with the current record.
Else open a message box to ask if the user wants to create
a new record. I am having trouble defining the condition
in the If command. Can you assist?

Thanks again,

Chappie
-----Original Message-----
In the Where condition, try
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#]
if GrantSumId is numeric
or
"[DLCDGrant#]= '" & [Forms]![frmGrantSumDE].[GrantSumID#] & "'"
if it is text

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
In a simple macro, I can use the following syntax in a
where condition to open a new form with the current record
displayed:

[DLCDGrant#]=[Forms]![frmGrantSumDE].[GrantSumID#]

However, when I include this language as a where condition
in the DoCmd.OpenForm action of VB, the correct record is
not opened. How do I change the syntax to make this
work? Do I need to declare a variable? If so, what would
the syntax of the variable be?

Thanks for any help.

Chappie
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Thanks again, Gerald. I think I have one remaining
problem. When a new record is opened, it does not display
the value of the linked field. Following is the code I am
using (it uses different field names than earlier posts
because I am using a different db.)

If IsNull(DLookup("[Grant#]", "tblCT", "[Grant#] = " &
[Forms]![frmtest].[ID])) Then
If MsgBox("Create a new tracking record?",
vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmCT", acNormal, , , acFormAdd
Else
MsgBox ("Action Cancelled")
End If
Else . . .
End If

How do I get the DoCmd.OpenForm to create/open a new
record with the same value in the Grant# field as the
value displayed in the current record in the frmtest form?

Chappie

-----Original Message-----
1) You are composing a string for the WHERE condition.
Your original statement was being translated into looking
for row(s) where the column [DLCDGrant#] was equal to the
string literal [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the double quotes and the & makes the
translation [DLCDGrant#] being equal to the value held in
the identifier [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the single quote is a JET requirement to
differentiate strings from numbers (and from dates, which
need to be enclosed in # marks) in comparisons.

2) You should be able to use the DLookUp function and
check for a Null Return indicating no match e.g.

If IsNull(DLookUp("[DLCDGrant#]", "YourTableName",
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE]. [GrantSumID#])) Then
MsgBox "No Matching Record"
Else
DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#]
Click to expand...
End If

Be sure to insert your own table name as the 2nd parameter
in the DLookUp statement.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald,
Your suggestion worked perfectly - now I have two follow-
ups.

1) Can you explain the use of ",' and & in your
examples. I know that double quotes define a string,
ampersand is concantenate and a single quote is a comment,
but that doesn't explain how they work together. Why
different for numeric versus text fields? I know this is
basic stuff, but it just doesn't make sense to me. A good
FAQ (which I haven't found) would be great too.

2) I intended to use the docmd as a part of an If
command. If there is a matching record in the second
table, use the docmd
((DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#])) to open the form with the current record.
Else open a message box to ask if the user wants to create
a new record. I am having trouble defining the condition
in the If command. Can you assist?

Thanks again,

Chappie
-----Original Message-----
In the Where condition, try
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#]
if GrantSumId is numeric
or
"[DLCDGrant#]= '" & [Forms]![frmGrantSumDE].
Click to expand...
[GrantSumID#]
& "'"
if it is text

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
In a simple macro, I can use the following syntax in a
where condition to open a new form with the current record
displayed:

[DLCDGrant#]=[Forms]![frmGrantSumDE].[GrantSumID#]

However, when I include this language as a where condition
in the DoCmd.OpenForm action of VB, the correct record is
not opened. How do I change the syntax to make this
work? Do I need to declare a variable? If so, what would
the syntax of the variable be?

Thanks for any help.

Chappie
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
If the form is not being opened as a modal, then you should
be able to set the control you want in the next line of
code after the DoCmd.OpenForm e.g. something along the
lines of
DoCmd.OpenForm "frmCT", acNormal, , , acFormAdd
Forms!frmCt!yourControl = [Forms]![frmtest].[ID]

If it is being opened as modal then the line of code should
go in its Load eventhandler e.g. something along the lines of
Me!yourControl = [Forms]![frmtest].[ID]

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Thanks again, Gerald. I think I have one remaining
problem. When a new record is opened, it does not display
the value of the linked field. Following is the code I am
using (it uses different field names than earlier posts
because I am using a different db.)

If IsNull(DLookup("[Grant#]", "tblCT", "[Grant#] = " &
[Forms]![frmtest].[ID])) Then
If MsgBox("Create a new tracking record?",
vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmCT", acNormal, , , acFormAdd
Else
MsgBox ("Action Cancelled")
End If
Else . . .
End If

How do I get the DoCmd.OpenForm to create/open a new
record with the same value in the Grant# field as the
value displayed in the current record in the frmtest form?

Chappie

-----Original Message-----
1) You are composing a string for the WHERE condition.
Your original statement was being translated into looking
for row(s) where the column [DLCDGrant#] was equal to the
string literal [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the double quotes and the & makes the
translation [DLCDGrant#] being equal to the value held in
the identifier [Forms]![frmGrantSumDE].[GrantSumID#].
The use of the single quote is a JET requirement to
differentiate strings from numbers (and from dates, which
need to be enclosed in # marks) in comparisons.

2) You should be able to use the DLookUp function and
check for a Null Return indicating no match e.g.

If IsNull(DLookUp("[DLCDGrant#]", "YourTableName",
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE]. [GrantSumID#])) Then
MsgBox "No Matching Record"
Else
DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#]
Click to expand...
End If

Be sure to insert your own table name as the 2nd parameter
in the DLookUp statement.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald,
Your suggestion worked perfectly - now I have two follow-
ups.

1) Can you explain the use of ",' and & in your
examples. I know that double quotes define a string,
ampersand is concantenate and a single quote is a comment,
but that doesn't explain how they work together. Why
different for numeric versus text fields? I know this is
basic stuff, but it just doesn't make sense to me. A good
FAQ (which I haven't found) would be great too.

2) I intended to use the docmd as a part of an If
command. If there is a matching record in the second
table, use the docmd
((DoCmd.OpenForm "frmCTrackGenInfoDE",
acNormal, , "[DLCDGrant#]= " & [Forms]![frmGrantSumDE].
[GrantSumID#])) to open the form with the current record.
Else open a message box to ask if the user wants to create
a new record. I am having trouble defining the condition
in the If command. Can you assist?

Thanks again,

Chappie
-----Original Message-----
In the Where condition, try
"[DLCDGrant#]= " & [Forms]![frmGrantSumDE].[GrantSumID#]
if GrantSumId is numeric
or
"[DLCDGrant#]= '" & [Forms]![frmGrantSumDE]. [GrantSumID#]
& "'"
if it is text

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
In a simple macro, I can use the following syntax in a
where condition to open a new form with the current
record
displayed:

[DLCDGrant#]=[Forms]![frmGrantSumDE].[GrantSumID#]

However, when I include this language as a where
condition
in the DoCmd.OpenForm action of VB, the correct record
is
not opened. How do I change the syntax to make this
work? Do I need to declare a variable? If so, what
would
the syntax of the variable be?

Thanks for any help.

Chappie
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding New Record with Data 1
Form Display 2
Syntax for opening a form with condition 2
Macro Email Variable 3
Where Statement on a subform/tab control 2
Open alternate form with criteria 2
how to reference a uniqueidentifier 2
Access Dcount (multiple criteria) 3

Back
Top