Type Mismatch

[soule]

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...

' 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("A1 Movie Code Table", dbOpenDynaset)
Set db = ("SELECT Forms.A1 Onboarding Tracking Form.[FirstName].Value, Forms.A1 Onboarding Tracking Form.[LastName].Value, Forms.A1 Onboarding Tracking Form.[KnownAs].Value, Forms.A1 Onboarding Tracking Form.[Title].Value, Forms.A1S1 Onboarding Tracking Form.[DidEmployeeDownload].Value FROM [A1 Onboarding Tracking Form]")

rs![FirstName] = Forms.A1 Onboarding Tracking Form.[FirstName].Value
rs![LastName] = Forms.A1 Onboarding Tracking Form.[LastName].Value
rs![KnownAs] = Forms.A1 Onboarding Tracking Form.[KnownAs].Value
rs![Title] = Forms.A1 Onboarding Tracking Form.[Title].Value
rs![DidEmployeeDownload] = Forms.A1 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.OpenRecordse t 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

[Rabbit]

I don't understand. Why are you doing this through code instead of binding the controls to the field in the table?

[C CSR]

Note 1) You've reset db to become your SQLstatement. Wrong.

[C CSR]

If the fields are not supposed to be bound to a table as suggested by Rabbit, you can use this syntax:

Sub sample1()
Dim Tbl1 As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set Tbl1 = db.OpenRecordset("A1 Movie Code Table",  dbOpenTable)
 
With Tbl1
    .AddNew
    !FirstName = [form control Value1?]
    !LastName = [form control Value2?]
    
    '.
    '.
    'etc where [form control Valuex] is the value of
    'whatever form/control you're referencing
    
    .Update
    .MoveNext
    .Close
End With
Set db = Nothing
End Sub

You could use an "INSERT INTO" sql statement but you'd have to put your values into it anyway.

If the control data is always kept in the same underlying table, set the form "Record Source" property to the table, then set each control in their properties to a field from the table (you can pull down a list of these options when you use the "propeties" list "Data" tab for anything on the form. If you don't set the entire form to a table, then you have to do it a little differently.

Does this make sense? (note: debug the code above, I didn't).

What do you want to do?

[C CSR]

Correction in the code I sent: remove the .movenext line. Sorry

[C CSR]

Further explanation:

your recordset is Tbl1. So, if I didn't use the "With Tbl1" line, the rest if the recordset calls would be:

Tbl1.AddNew
Tbl1!FirstName
Tbl1.Update
Tbl1.Close
etc.

[C CSR]

If you set the form and controls to the underlying table, you don't need to call the Sample1 code. As soon as you change records or exit, the data is already placed in the table. If you're using a button as a "Save Record" feature, you can set the button's onclick event (under button properties) to an embedded macro which offers the Save option for Records in a little wizard. Access handles the codeing.

[NeoPa]

This all seems very strange, possibly because the title is so wrong (I'll fix that shortly).

The problem is that you're trying, on line #11, to set a database variable to a SQL formatted string. It doesn't make a lot of sense and is clearly not right.

PS. You really should pay close attention to Rabbit's post too. It may alter your approach somewhat and save you untold hours of wasted development time down the line. Don't say we didn't warn you ;-)

[soule]

Hi, guys -- thank you so much for taking the time to reply to my stuff. You guys are really saving my butt. I'm a neophite vb user who's been asked to write some automation code on a consulting job!

I have a db with one form and two tables. All but the following of my form controls are bound to my "tracking" table. I want to have a lostfocus event on my form's "Movie Code" control whereby it will be populated from my "movie code" table that includes movie codes entered in blocks once every month by my boss. I was told a control must be unbound if you want to populate it with table data. After the movie code control populates when it loses focus, then...the form control after the "Movie Code" control is the "Movie Code Send Date" control that IS bound to the movie code table. The user should then enter the current day's date and continue tabbing to the next control (I don't want another lostfocus event here, though maybe I should). So, when the user hits my e-mail automation button, the record should be saved but still visible in form, and the data in the FirstName, LastName, KnownAs, Title and DidEmployeeDown load controls of that current record should populate the same in the "movie code" table.

While my "movie code send date" IS bound to the "sub-table", the reason I don't create a field in my main tracking table and source the lostfocus event "movie code" control to it is because the situation requires a table with both "used" and "unused" mixed data and I need a separate table for my boss to enter her monthly list of unused movie codes into.

My revised code for that piece is as follows:

' 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

rs.Edit

rs![FirstName] = Forms.[A1 Onboarding Tracking Form].[FirstName].Value
rs![LastName] = Forms.[A1 Onboarding Tracking Form].[LastName].Value
rs![KnownAs] = Forms.[A1 Onboarding Tracking Form].[KnownAs].Value
rs![Title] = Forms.[A1 Onboarding Tracking Form].[Title].Value

rs.Update
rs.Close

Debug.Print ("Populate used movie code recipient info. to movie code table")

Set rs = Nothing
Set db = Nothing

Exit Sub

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

I dropped the OpenRecordset/SELECT statement as it doesn't seem to be needed in this case. I also added an rs.Edit after my Set statement, an rs.Update before rs.Close, and bracketed my spaced field names correctly.

Does this seem to be free of any major snags now?

Note: I can't seem to make reference to any DAO version in the library. Am I working in ADO in VB 6.5?

[NeoPa]

@Frank.
You need to be telling us what you're working with, not vice-versa. Are you not working from Access VBA? It's important to know.
DAO is what you should be using for this assuming you should even be thinking of doing it this way. I see no response to the point that Rabbit & I both drew your attention to. Forms were designed to be simple and do much of this for you. Why are you choosing to try to redo all that yourself? It makes little sense and I try to avoid telling people how to go the wrong way about doing something. If you continue on this path all your work will be X times more complicated than it needs to be.

[soule]

I'm working from Access 2007, VBA 6.5. There is neither a DAO or ADO or ADO.NET object library specified in my checked library items. I can't find the info. on which library VBA 6.5 references automatically (or if it does at all) quick enough with the materials I have available. Some sources are telling me 6.5 defaults to DAO. Some say ADO. Some say I have to check them in library to use them - I tried that and get errors. Anyone know?

As far as control sourcing, I was told that a form control that a table draws from can't be bound. Anyone know?

This situation requires me to populate a form control FROM a table field, and then populate different fields in that table FROM more/different form controls.

Frank

[NeoPa]

My library (Access 2003) is called "Microsoft DAO 3.6 Object Library". I expect yours will be a later version, but will have a similarly formatted name. It certainly should be selected in your project if you want to use it.

Soule:
As far as control sourcing, I was told that a form control that a table draws from can't be bound. Anyone know?

I have no idea what you're trying to ask or even what you're referring to. Tables don't draw forms.

[soule]

I don't know if I want to use DAO. Ha. That's been my question all along. I can't seem to get a straight answer from any source on what version of Jet engine Access 2007/VB 6.5 uses let alone if it can process DAO, ADO, ADO.NET, ODBC, etc.. Anyways, when I try to add my latest DAO object library (also the MS DAO 3.6) I get a "name conflicts with existing module, project or object library" error msgbox (which I'm thinking isn't likely from having my private code in a standard module).

"As far as control sourcing, I was told that a form control that a table draws from can't be bound."...meani ng the data in a form control is populated into a table that is a different (minor) table than almost all the other form controls are bound to can't be bound to ANY table or the procedure won't work. Draws meaning "is populated with same data" if it meets the search criteria.

[C CSR]

Access 2007. Mine says 12.0 Object Library & the 12.0 Engine object library. I've never changed anything yet. This is by default.

Looking at your tasks explanation, I'll try to fill in some info piece by piece. NeoPa is the heavyweight here, so just disregard my stuff when he overides it.:

1) "I was told a control must be unbound if you want to populate it with table data." Answer: write an sqlStr to dig out the table data/list you want in the control and stick it in the "Row Source" property line for an "unbound" drop or list control. Then you can manipulate the string "Onchange," etc. if you find a need for that.

Soule:

I can't work with the 3rd paragraph of your reply #9. Please split that up, maybe reference the controls with a number or something. The tasks your explaining run over each other. (no offense) Slow down and Break it down. What would your friends think if they had to read that :)


You wrote: "I dropped the OpenRecordset/SELECT statement as it doesn't seem to be needed in this case." Answer: You can't put data into a table unless you open it.
You need the Set rs = dbs.OpenRecords et("YourTable" , dbOpenTable) to use the line that follow. Are you just writing code or are you testing it as you go?

[NeoPa]

Soule:
I don't know if I want to use DAO. Ha. That's been my question all along.

NeoPa:
DAO is what you should be using for this

Frank. I believe this was as clear an answer as you could require (from post #10).

Soule:
Draws meaning "is populated with same data" if it meets the search criteria.

I'm here to tell you that's not what it means. That's simply a misuse of the language and if you try to use it that way then there will be confusion.

Anyway, what you were told is not strictly true. Certainly there can only be one record source for a bound form, but that record source can be (and often is) a query that includes fields from multiple tables. How your data interacts and how appropriate it is to connect this data only you can say at this point, as we don't have that information available yet, but the fact that you are considering including it on the same form generally indicates the connection is relatively straightforward in one way or another (IE. The data is somehow logically linked).