Help with strSQL

S

S. Jackson

Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
 
D

Dan Artuso

Hi,
Here is how to do what you want.

This code will go in the AfterUpdate event of your
RptFilecmbo.

Dim rs As DAO.Recordset

'set rs equal to your form's recordset
Set rs = Me.RecordsetClone

'let's see if the record exists
rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

'if it does, move the form to that record
'if it doesn't, move the form to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'fill in RptName control
Me.RptName = Me.RptFilecmbo
Else
'it exists so move the form to that record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

One thing, make sure you have a reference to DAO set.
With any code module open, go to Tools->References
and check DAO.xxx where xxx is the version
 
D

Dan Artuso

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP
 
S

S Jackson

Dan!

Thank you so much for your help. But, I had to change it up a little bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code does
not work. It inserts the RptFileName instead of the RptName. (of course,
the RptName field will be blank if the user has created a new report not yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Dan Artuso said:
Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




S. Jackson said:
Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
 
S

S Jackson

Correction. The last code that updates the control RptName, does not work
at all (I messed up and had its data source set to RptFileName instead of
RptName). When I made the change to RptName, now it just remains blank even
though I've picked a record that exists in tblReports. Do I need the
Find.First and Bookmark stuff?

S. Jackson

Dan Artuso said:
Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




S. Jackson said:
Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
 
S

S Jackson

Gaack!!!

Okay. I'm so ignorant!!!

I realize I do not need to use an AfterUpdateEvent on the Hidden control
RptFileName. For some reason it does not fire anyway.

I should be able to populate the RptName and RptDescription controls in the
AfterUpdate Event on the RptFilecmbo

HOWEVER! Now I am getting an error if I select a RptFileName from the
RptFilecmbo that does not exist in tblReports. The error is: Run Time 3020
Update or Cancel Update without AddNew or Edit. The code stops at:

Me.Bookmark = rs.Bookmark

(This is what happens when I start messing with stuff I don't fully
understand. Yeesh!)

S. Jackson

S Jackson said:
Correction. The last code that updates the control RptName, does not work
at all (I messed up and had its data source set to RptFileName instead of
RptName). When I made the change to RptName, now it just remains blank even
though I've picked a record that exists in tblReports. Do I need the
Find.First and Bookmark stuff?

S. Jackson

Dan Artuso said:
Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




S. Jackson said:
Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
D

Dan Artuso

Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


S Jackson said:
Dan!

Thank you so much for your help. But, I had to change it up a little bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code does
not work. It inserts the RptFileName instead of the RptName. (of course,
the RptName field will be blank if the user has created a new report not yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Dan Artuso said:
Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




S. Jackson said:
Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Dan:

I think I've finally puzzled this out. My code in the AfterUpdate Event of
the RptFilecmbo box does not include code to move the form to the matching
record:

Dim rs As DAO.Recordset
Dim strSQL As String

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record

(((((( Here is where I am missing code to move the form to that record )))))

'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark

End If

'release the recordset object
Set rs = Nothing


Dan Artuso said:
Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


S Jackson said:
Dan!

Thank you so much for your help. But, I had to change it up a little bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code does
not work. It inserts the RptFileName instead of the RptName. (of course,
the RptName field will be blank if the user has created a new report not yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Dan Artuso said:
Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and have
this selection added to tblReports if not in the table. Then I want the
remaining controls that are bound to the tblReports to display
corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
D

Dan Artuso

Hi,
This code:
Me.Bookmark = rs.Bookmark

moves the form to the matching record.
So, it sounds as though FindFirst is never finding a match.

Take look at this code carefully to understand what it does:

If rs.NoMatch Then
'if we're here it means no match was found
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'if we're here it means we found a match
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

try putting:
MsgBox "RptFileName = '" & Me.RptFilecmbo & "'"

just before this line:
rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

Post the results of the message box.

HTH
Dan Artuso, MVP



S Jackson said:
Dan:

I think I've finally puzzled this out. My code in the AfterUpdate Event of
the RptFilecmbo box does not include code to move the form to the matching
record:

Dim rs As DAO.Recordset
Dim strSQL As String

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record

(((((( Here is where I am missing code to move the form to that record )))))

'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark

End If

'release the recordset object
Set rs = Nothing


Dan Artuso said:
Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


S Jackson said:
Dan!

Thank you so much for your help. But, I had to change it up a little bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code does
not work. It inserts the RptFileName instead of the RptName. (of course,
the RptName field will be blank if the user has created a new report
Click to expand...
not
yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box
Click to expand...
and
have
this selection added to tblReports if not in the table. Then I
Click to expand...
want
the
remaining controls that are bound to the tblReports to display
corresponding
information if the user happens to select a report already in the table.

My first attempt is failing because I am getting the actualy sql string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Dan:

Thank you for all of your help. After I posted that last post, I realized
that the code Me.Bookmark = rs.Bookmark was the code to move the form. My
apologies.

I started over on this form. It is so bizarre. I started over with a brand
new form and put in all the controls and code and it worked. Saved it and
then when I opened it, it would only displayed one record. There are
currently 14 records in tblRecords, so the form should have indicated 14
records.

So, I started over again and designed another form, but this time, I saved
it, closed it and reopened the form after each change. I also did not copy
and paste from any of my previous attempts.

So far, it seems to be working. I am still mystified as to what went wrong
with my prior attempts. It just doesn't make sense and seems a little buggy
to me.

Again, thank you for all of your attempts to help me. I have learned far
more here than I have from the books I bought. I need to learn by trial and
error (I seem to have a lot of error!).

Thank you again.
S. Jackson

Dan Artuso said:
Hi,
This code:
Me.Bookmark = rs.Bookmark

moves the form to the matching record.
So, it sounds as though FindFirst is never finding a match.

Take look at this code carefully to understand what it does:

If rs.NoMatch Then
'if we're here it means no match was found
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'if we're here it means we found a match
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

try putting:
MsgBox "RptFileName = '" & Me.RptFilecmbo & "'"

just before this line:
rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

Post the results of the message box.

HTH
Dan Artuso, MVP



S Jackson said:
Dan:

I think I've finally puzzled this out. My code in the AfterUpdate Event of
the RptFilecmbo box does not include code to move the form to the matching
record:

Dim rs As DAO.Recordset
Dim strSQL As String

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record

(((((( Here is where I am missing code to move the form to that record )))))

'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark

End If

'release the recordset object
Set rs = Nothing


Dan Artuso said:
Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


Dan!

Thank you so much for your help. But, I had to change it up a
Click to expand...
little
bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code does
not work. It inserts the RptFileName instead of the RptName. (of course,
the RptName field will be blank if the user has created a new report not
yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box and
have
this selection added to tblReports if not in the table. Then I want
the
remaining controls that are bound to the tblReports to display
corresponding
information if the user happens to select a report already in the
table.

My first attempt is failing because I am getting the actualy sql
string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

I sure hope you can follow this thread. My apologies for it being so
confusing, but I did not want to get in trouble for starting a new thread
for the same subject. Hopefully you will see this message:

The code you gave me works beautifully only IF the user does not put spaces
into the file name of his newly created report. If the user names his
report "My Invoices" instead of "MyInvoices" (no space), the code generates
the following error:

Runtime error 3077
Syntax error (missing operator) in expression
This line is highlighted on the debug screen: rs.FindFirst "RptFileName =
'" & Me.RptFilecmbo & "'"

Any thoughts?

S. Jackson



S Jackson said:
Dan:

Thank you for all of your help. After I posted that last post, I realized
that the code Me.Bookmark = rs.Bookmark was the code to move the form. My
apologies.

I started over on this form. It is so bizarre. I started over with a brand
new form and put in all the controls and code and it worked. Saved it and
then when I opened it, it would only displayed one record. There are
currently 14 records in tblRecords, so the form should have indicated 14
records.

So, I started over again and designed another form, but this time, I saved
it, closed it and reopened the form after each change. I also did not copy
and paste from any of my previous attempts.

So far, it seems to be working. I am still mystified as to what went wrong
with my prior attempts. It just doesn't make sense and seems a little buggy
to me.

Again, thank you for all of your attempts to help me. I have learned far
more here than I have from the books I bought. I need to learn by trial and
error (I seem to have a lot of error!).

Thank you again.
S. Jackson

Dan Artuso said:
Hi,
This code:
Me.Bookmark = rs.Bookmark

moves the form to the matching record.
So, it sounds as though FindFirst is never finding a match.

Take look at this code carefully to understand what it does:

If rs.NoMatch Then
'if we're here it means no match was found
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'if we're here it means we found a match
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

try putting:
MsgBox "RptFileName = '" & Me.RptFilecmbo & "'"

just before this line:
rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

Post the results of the message box.

HTH
Dan Artuso, MVP



S Jackson said:
Dan:

I think I've finally puzzled this out. My code in the AfterUpdate
Click to expand...
Event
of
the RptFilecmbo box does not include code to move the form to the matching
record:

Dim rs As DAO.Recordset
Dim strSQL As String

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record

(((((( Here is where I am missing code to move the form to that record )))))

'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark

End If

'release the recordset object
Set rs = Nothing


Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


Dan!

Thank you so much for your help. But, I had to change it up a little
bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control source is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the
next
control on my form - tblReports[RptName]. This field is a user friendly
name for the report (instead of the file name). The following code
does
not work. It inserts the RptFileName instead of the RptName. (of
course,
the RptName field will be blank if the user has created a new
Click to expand...
report
not
yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo
Click to expand...
box
and
have
this selection added to tblReports if not in the table. Then
Click to expand...
I
want
the
remaining controls that are bound to the tblReports to display
corresponding
information if the user happens to select a report already in the
table.

My first attempt is failing because I am getting the actualy sql
string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner VBA)?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Yikes! My fingers are faster than my brain.

The code only fails if the user inserts something like and ' in the file
name, i.e., Sally's Report, not if the user puts in a space in the file
name.

I'm going to leave it the way it is and take a chance that the user will not
use any apostrophe's when naming their reports (cringe)

S. Jackson

S Jackson said:
I sure hope you can follow this thread. My apologies for it being so
confusing, but I did not want to get in trouble for starting a new thread
for the same subject. Hopefully you will see this message:

The code you gave me works beautifully only IF the user does not put spaces
into the file name of his newly created report. If the user names his
report "My Invoices" instead of "MyInvoices" (no space), the code generates
the following error:

Runtime error 3077
Syntax error (missing operator) in expression
This line is highlighted on the debug screen: rs.FindFirst "RptFileName =
'" & Me.RptFilecmbo & "'"

Any thoughts?

S. Jackson



S Jackson said:
Dan:

Thank you for all of your help. After I posted that last post, I realized
that the code Me.Bookmark = rs.Bookmark was the code to move the form. My
apologies.

I started over on this form. It is so bizarre. I started over with a brand
new form and put in all the controls and code and it worked. Saved it and
then when I opened it, it would only displayed one record. There are
currently 14 records in tblRecords, so the form should have indicated 14
records.

So, I started over again and designed another form, but this time, I saved
it, closed it and reopened the form after each change. I also did not copy
and paste from any of my previous attempts.

So far, it seems to be working. I am still mystified as to what went wrong
with my prior attempts. It just doesn't make sense and seems a little buggy
to me.

Again, thank you for all of your attempts to help me. I have learned far
more here than I have from the books I bought. I need to learn by trial and
error (I seem to have a lot of error!).

Thank you again.
S. Jackson
Click to expand...
source
is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically populate the
next
control on my form - tblReports[RptName]. This field is a user
friendly
name for the report (instead of the file name). The following code
does
not work. It inserts the RptFileName instead of the RptName. (of
course,
the RptName field will be blank if the user has created a new report
not
yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the combo box
and
have
this selection added to tblReports if not in the table.
Click to expand...
Click to expand...
Then
I in
the
Click to expand...
Click to expand...
 
D

Dan Artuso

Hi,
If you're worried about apostrophe's, use this instead:
rs.FindFirst "RptFileName = " & Chr(34) & Me.RptFilecmbo & Chr(34)

HTH
Dan Artuso, MVP

S Jackson said:
Yikes! My fingers are faster than my brain.

The code only fails if the user inserts something like and ' in the file
name, i.e., Sally's Report, not if the user puts in a space in the file
name.

I'm going to leave it the way it is and take a chance that the user will not
use any apostrophe's when naming their reports (cringe)

S. Jackson

S Jackson said:
I sure hope you can follow this thread. My apologies for it being so
confusing, but I did not want to get in trouble for starting a new thread
for the same subject. Hopefully you will see this message:

The code you gave me works beautifully only IF the user does not put spaces
into the file name of his newly created report. If the user names his
report "My Invoices" instead of "MyInvoices" (no space), the code generates
the following error:

Runtime error 3077
Syntax error (missing operator) in expression
This line is highlighted on the debug screen: rs.FindFirst "RptFileName =
'" & Me.RptFilecmbo & "'"

Any thoughts?

S. Jackson



S Jackson said:
Dan:

Thank you for all of your help. After I posted that last post, I realized
that the code Me.Bookmark = rs.Bookmark was the code to move the form. My
apologies.

I started over on this form. It is so bizarre. I started over with a brand
new form and put in all the controls and code and it worked. Saved it and
then when I opened it, it would only displayed one record. There are
currently 14 records in tblRecords, so the form should have indicated 14
records.

So, I started over again and designed another form, but this time, I saved
it, closed it and reopened the form after each change. I also did not copy
and paste from any of my previous attempts.

So far, it seems to be working. I am still mystified as to what went wrong
with my prior attempts. It just doesn't make sense and seems a little buggy
to me.

Again, thank you for all of your attempts to help me. I have learned far
more here than I have from the books I bought. I need to learn by
Click to expand...
trial
and
error (I seem to have a lot of error!).

Thank you again.
S. Jackson

Hi,
This code:
Me.Bookmark = rs.Bookmark

moves the form to the matching record.
So, it sounds as though FindFirst is never finding a match.

Take look at this code carefully to understand what it does:

If rs.NoMatch Then
'if we're here it means no match was found
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'if we're here it means we found a match
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

try putting:
MsgBox "RptFileName = '" & Me.RptFilecmbo & "'"

just before this line:
rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

Post the results of the message box.

HTH
Dan Artuso, MVP



Dan:

I think I've finally puzzled this out. My code in the AfterUpdate Event
of
the RptFilecmbo box does not include code to move the form to the
matching
record:

Dim rs As DAO.Recordset
Dim strSQL As String

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record

(((((( Here is where I am missing code to move the form to that
record )))))

'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptFileName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark

End If

'release the recordset object
Set rs = Nothing


Hi,
Your question does not make sense to me.
If the RptFileName chosen in the combo already exists,
the form should display that record with all the controls populated.

If the RptFileName chosen in the combo is not in your tblReports,
then it goes to a new record and the user has to fill things in.

In other words, you should not have to write extra code to get RptName
displayed.

HTH
Dan Artuso, MVP


Dan!

Thank you so much for your help. But, I had to change it up a
little
bit.
My tblReports has the following fields:
RptFileName
RptName
RptDescription

I have added a hidden text box to the form whose's control
Click to expand...
source
is
tblReports[RptFileName]
The following code updates it correctly:

Dim rs As DAO.Recordset

'Make recordset the same as the form's recordset:

Set rs = Me.RecordsetClone

'See if the record exists in tblReports

rs.FindFirst "RptFileName = '" & Me.RptFilecmbo & "'"

'If it does, move the form to that record
'If it does not, go to a new record

If rs.NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate RptName
Me.RptFileName = Me.RptFilecmbo

Else
'its there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release the recordset object
Set rs = Nothing

End Sub

But, here is the next problem. I want to automatically
Click to expand...
populate
the
next
control on my form - tblReports[RptName]. This field is a user
friendly
name for the report (instead of the file name). The
Click to expand...
following
code
does
not work. It inserts the RptFileName instead of the RptName. (of
course,
the RptName field will be blank if the user has created a new report
not
yet
added to tblReports).

Dim strSQL As String

strSQL = "Select tblReports.RptName" _
& " FROM tblReports" _
& " WHERE tblReports.RptFileName = " & Me.RptFileName & " "

Me.RptName = strSQL

Can you help me fix this? Thanks.
S. Jackson

Hi,
Here is the code to do what you want:
This will go in the AfterUpdate event of RptFilecmbo

*untested*

Dim rs As DAO.Recordset

'make rs the same as the form's recordset
Set rs = Me.RecordsetClone

'see if the record exists
'if it does, move the form to that record
'if it doesn't, go to a new record

rs.FindFirst "RptName = '" & Me.RptFilecmbo & "'"

If rs.NoMatch Then
'it's not there
DoCmd.GoToRecord asDataForm, Me.Name, acNewRec
'populate RptName
Me.RptName = Me.RptFilecmbo
Else
'it's there so move the form to the record
Me.Bookmark = rs.Bookmark
End If

'release our recordset object
Set rs = Nothing

'***end code***

Make sure you have a reference to DAO set.
With any code module open, go to Tools->References
make sure DAO.xxx is checked (xxx is the version)

HTH
Dan Artuso, MVP




Form is named fsubRpts. Control source: tblReports

First control is an unbound combo box. Source:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764;

Second control is bound text box. Source tblReports[RptName].

I want the user to select a Report file name from the
Click to expand...
combo
box
and
have
this selection added to tblReports if not in the table.
Click to expand...
Then
I
want
the
remaining controls that are bound to the tblReports to display
corresponding
information if the user happens to select a report already in
the
table.

My first attempt is failing because I am getting the
Click to expand...
actualy
sql
string
pasted into the first control:

Dim strSQL As String

strSQL = "Select tblReports.RptName, " _
& "FROM tblReports " _
& "WHERE tblReports.RptName = " & Me.RptFilecmbo & ""

Me.RptName = strSQL

How do I fix this, or is it too complicated for me (beginner
VBA)?
S. Jackson
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

More Help Needed - Report Objects 3
Report List Box 1
View report from Drop Down List 2
strSQL as String 3
List Box in a Dialog Form 3
Set rst = dbs.OpenRecordset(strSql) 5
Help With "Like" in strSQL 4
using system tables in queries 5

Top