Hit Statement & Syntax Wall (1 Viewer)

Soule · Jan 17, 2012

Hi, Everyone,
A little fire to put out --

I'm trying to simply populate 5 fields in a table from the same-named 5 controls on a form with vb OnButtonClick(). Four of the controls are sourced to another table, and one of them us unbound. My code is in an .accdb file in Access 2007. I have no ADO library ref. specified. Should I?

I'm getting my first error ("type mismatch") on the SELECT line. Here it is as it sits...

Code:

' This final procedure copies the data in the "FirstName", "LastName", "KnownAs", "Title" and "DidEmployeeDownload" form
' controls  to same named fields in "A1 Movie Code Table".
      
On Error GoTo PROC_ERR
Dim db As Database
Dim rs As Recordset
  
Set db = CurrentDb
Set rs = db.OpenRecordset("A1S1 Movie Code Table", dbOpenDynaset)
Set db = ("SELECT Forms.A1S1 Onboarding Tracking Form.[FirstName].Value, Forms.A1S1 Onboarding Tracking Form.[LastName].Value, Forms.A1S1 Onboarding Tracking 
Form.[KnownAs].Value, Forms.A1S1 Onboarding Tracking Form.[Title].Value, Forms.A1S1 Onboarding Tracking Form.[DidEmployeeDownload].Value FROM [A1S1 
Onboarding Tracking Form]")

 
rs![FirstName] = Forms.A1S1 Onboarding Tracking Form.[FirstName].Value
rs![LastName] = Forms.A1S1 Onboarding Tracking Form.[LastName].Value
rs![KnownAs] = Forms.A1S1 Onboarding Tracking Form.[KnownAs].Value
rs![Title] = Forms.A1S1 Onboarding Tracking Form.[Title].Value
rs![DidEmployeeDownload] = Forms.A1S1 Onboarding Tracking Form.[DidEmployeeDownload].Value
 
rs.Close
 
Debug.Print ("Populate used movie code recipient info. to movie code table")
 
PROC_EXIT:
Set rs = Nothing
Set db = Nothing

 
Exit Sub
 
PROC_ERR:
MsgBox "Error populating used movie code recipient info. to movie code table." & vbCrLf & Err.Number & Err.Description, vbExclamation + vbOKOnly, "Populate 
Table Next Movie Code To Form Control"
  
Resume PROC_EXIT

Should I not be calling the table in the db.OpenRecordset set statement? Is its syntax right?

What is wrong with my Set db/SELECT line? Are they incompatible statements together?

I feel like I'm close but am missing something here in my Set and rs! lines.

Any input will be appreciated. Thank you.

Frank

pbaldy · Jan 17, 2012

You already set db, correctly. The SELECT would be appropriate for setting the rs variable, not the db variable.

pbaldy · Jan 17, 2012

I guess I should add that I don't think you need that line at all, given what you seem to be trying to do. You will get errors on these lines:

rs![FirstName] = Forms.A1S1 Onboarding Tracking Form.[FirstName].Value

Due to the inadvisable spaces in your names, they need to be bracketed. Also, you either need the AddNew or Edit methods of the recordset specified.

nanscombe · Jan 17, 2012

Not to mention the Update method to save the changes once you've made them.

This is how I would see you code. I've added some bits in bold.

Code:

' This final procedure copies the data in the "FirstName", "LastName", "KnownAs", "Title" and "DidEmployeeDownload" form
' controls  to same named fields in "A1 Movie Code Table".
      
On Error GoTo PROC_ERR
Dim db As Database
Dim rs As Recordset
[B]Dim strSearch as string[/B]
 
Set db = CurrentDb
Set rs = db.OpenRecordset("A1S1 Movie Code Table", dbOpenDynaset)

[B]' Create a string to search for a pre-existing record based on the name
strSearch = "[FirstName] = '" & Forms.[A1S1 Onboarding Tracking Form].[FirstName] & "'"
strSearch = strSearch & " AND [LastName] = '" & Forms.[A1S1 Onboarding Tracking Form].[LastName] & "'"

' Look for the record
rs.FindFirst strSearch

IF rs.noMatch then
' If no record exists then add one
  rs.addnew

Else
' If record exists then edit it
  rs.edit

End If[/B]

rs![FirstName] = Forms.[A1S1 Onboarding Tracking Form].[FirstName]
rs![LastName] = Forms.[A1S1 Onboarding Tracking Form].[LastName]
rs![KnownAs] = Forms.[A1S1 Onboarding Tracking Form].[KnownAs]
rs![Title] = Forms.[A1S1 Onboarding Tracking Form].[Title]
rs![DidEmployeeDownload] = Forms.[A1S1 Onboarding Tracking Form].[DidEmployeeDownload]

[B]' Save your changes
rs.update[/B]

rs.Close
 
Debug.Print ("Populate used movie code recipient info. to movie code table")
 
PROC_EXIT:
Set rs = Nothing
Set db = Nothing

 
Exit Sub
 
PROC_ERR:
MsgBox "Error populating used movie code recipient info. to movie code table." & vbCrLf & Err.Number & Err.Description, vbExclamation + vbOKOnly, "Populate 
Table Next Movie Code To Form Control"
  
Resume PROC_EXIT

Soule · Jan 17, 2012

Thanks, guys. I am taking pbaldy's advice and dropping the openRecordset/SELECT line for now. When I did that, I realized that that procedure was now error free. It makes sense just to declare the db and rs, and then get right into the parallel data equations using an rs.Edit statement. I then followed those rs![myfield] lines with an rs.Update before my rs.Close.

Thanks, all. You guys are really saving my ass. This is my first time using vb and I've been professionally challenged to do automation code!

Onward!

pbaldy · Jan 17, 2012

You realize that will edit the first record in the table? Presuming you want to edit a specific record, I'd open the recordset on an SQL statement that only retrieved that record.

Soule · Jan 17, 2012

Yikes. You're right. I'll go with nanscombe's tack.

To be continued...(probably)...

pbaldy · Jan 18, 2012

It could work, but I wouldn't go that way. It pulls the entire table into the recordset instead of just the desired record. You'd probably notice a performance difference if the table is large.

Soule · Jan 18, 2012

pbaldy,
Thanks, I'll give that some thought.

nanscome,
I'm confused about the syntax of the strSearches...

Code:

strSearch = "[MovieCode] = '" & Forms.[A1 Onboarding Tracking Form].[MovieCode] & "'"
strSearch = strSearch & " AND [MovieCode] = '" & Forms.[A1 Onboarding Tracking Form].[MovieCode] & "'"

Everytime I think I know a " string convention, I seem to see a contradictory use of it.

Could you definitively break down for me what you did above and how the single and double quotes function?

Thank you so much for your time.

Frank

nanscombe · Jan 18, 2012

The double quotes (") are used as the delimiter for the search string that I'm building.

The single quotes(') are because the fields being searched are (presumably) text fields.

I will assume the form has the fields FirstName - John and the LastName - Smith.

It would get more confusing if you used double quotes to build the search string because you would need to use two together ("") to represent a single one otherwise Access would think it saw the end of a string instead of a quote.

Code:

strSearch = "[FirstName] = """ & [B]John[/B] & """ AND [LastName] = """ & [B]Smith[/B] & """"

So using a single quote, in this instance, makes it easier to code.

Code:

strSearch = "[FirstName] = '" & [B]John[/B] & "' AND [LastName] = '" & [B]Smith[/B] & "'"

Soule · Jan 18, 2012

Nanscombe, thanks for taking the time to reply to my string question. I needed to retrain my eye to see the ampersands as "separators" when looking at the whole search statement. Thanks.

nanscombe said:
I will assume the form has the fields FirstName - John and the LastName - Smith.

Yes, my form has the fields FirstName, LastName, KnownAs and Title.

So, do I need two lines of string search? I still don't understand the structure.

The first strSearch line defines that the table field is text and then searches the form field and then "any text"?

Code:

strSearch = "[MovieCode] = '" & Forms.[A1 Onboarding Tracking Form].[MovieCode] & "'"

The second strSearch searches the first strSearch, then re-defines the table field is text, then searches the form field and then "any text"?

Code:

strSearch = strSearch & " AND [MovieCode] = '" & Forms.[A1 Onboarding Tracking Form].[MovieCode] & "'"

I can't find good examples of strSearch structure in either of the vba library books I have or on any webpage!

nanscombe · Jan 18, 2012

strSearch just represents the WHERE clause of an SQL statement, so that might provide better examples than VBA.

Code:

strSearch = "This searches for where the first field = '" & TheFirstValue & "'"
strSearch = strSearch & " [B]AND[/B] this searches where a second field = '" & aSecondString & "'"
strSearch = strSearch & " [B]AND[/B] this would search where a third field = '" & aThirdString & "'"

The Ampersand isn't being used as a separator but to join (concatenate) the separate lines together to make one long string.

Soule · Jan 18, 2012

Thanks, nascombe!

Since I know the table field "moviecode" (the third field from the left) is the one I want to search for, will using this one line which references the specific "field2" do the trick in a search statement?

Code:

strSearch = "[MovieCode]field2 = '" & Forms.[A1S1 Onboarding Tracking Form].[MovieCode] & "'"

Also, why do I need the wildcard -- & "'" -- at the end of the line(s)? Does it represent that the data in the form is text?

Thank you so much.

Frank

nanscombe · Jan 18, 2012

The single quote at the end is not a wildcard it's just the closing quote delimiter around the text string.

Code:

strSearch = "[MovieCode].field2 = '" & Forms.[A1S1 Onboarding Tracking Form].[MovieCode] & "'"

The double quotes (") define the starting and ending points of the parts which make up the text string strSearch.

The single quotes(') delimit the string passed in from the form control MovieCode which I am assuming is a text field.

Code:

strSearch = "[MovieCode].field2 = 'theValueInTheField[A1S1 Onboarding Tracking Form].[MovieCode]'"

Hit Statement & Syntax Wall (1 Viewer)

Soule

Registered User.

pbaldy

Wino Moderator

pbaldy

Wino Moderator

nanscombe

Registered User.

Soule

Registered User.

pbaldy

Wino Moderator

Soule

Registered User.

pbaldy

Wino Moderator

Soule

Registered User.

nanscombe

Registered User.

Soule

Registered User.

nanscombe

Registered User.

Soule

Registered User.

nanscombe

Registered User.

Similar threads

Users who are viewing this thread