By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Type Mismatch

P: 34
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...

Expand|Select|Wrap|Line Numbers
  1. ' This final procedure copies the data in the "FirstName", "LastName", "KnownAs", "Title" and "DidEmployeeDownload" form
  2. ' controls  to same named fields in "A1 Movie Code Table".
  3.  
  4. On Error GoTo PROC_ERR
  5.  
  6. Dim db As Database
  7. Dim rs As Recordset
  8.  
  9. Set db = CurrentDb
  10. Set rs = db.OpenRecordset("A1 Movie Code Table", dbOpenDynaset)
  11. 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]")
  12.  
  13. rs![FirstName] = Forms.A1 Onboarding Tracking Form.[FirstName].Value
  14. rs![LastName] = Forms.A1 Onboarding Tracking Form.[LastName].Value
  15. rs![KnownAs] = Forms.A1 Onboarding Tracking Form.[KnownAs].Value
  16. rs![Title] = Forms.A1 Onboarding Tracking Form.[Title].Value
  17. rs![DidEmployeeDownload] = Forms.A1 Onboarding Tracking Form.[DidEmployeeDownload].Value
  18.  
  19. rs.Close
  20.  
  21. Debug.Print ("Populate used movie code recipient info. to movie code table")
  22.  
  23. PROC_EXIT:
  24. Set rs = Nothing
  25. Set db = Nothing
  26.  
  27. Exit Sub
  28.  
  29. PROC_ERR:
  30. MsgBox "Error populating used movie code recipient info. to movie code table." & vbCrLf & Err.Number & Err.Description, vbExclamation + vbOKOnly, "Populate 
  31.  
  32. Table Next Movie Code To Form Control"
  33.  
  34. 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
Jan 17 '12 #1

✓ answered by C CSR

Soule:

I'm not trying to sell you a dropdown, but know this:

1) You can use a dropdown for dynamically changing lists. They change as the underlying table changes.

2) If your data is well thought out and properly grouped, you can use the same dropdown for multiple lists. Example:

a -- list say cars, planes, boats. Onclick, list now says
b -- gas, diesel, ethenol. Onclick, list now says
c -- red, green blue, purple. Onclick.....etc. on and on.

You don't have to use a dropbox; you can use a list.

Do you want to know how? Explain your data, in detail (i.e, 1), 2) 3),....)

Share this Question
Share on Google+
31 Replies


Rabbit
Expert Mod 10K+
P: 12,366
I don't understand. Why are you doing this through code instead of binding the controls to the field in the table?
Jan 17 '12 #2

100+
P: 144
Note 1) You've reset db to become your SQLstatement. Wrong.
Jan 17 '12 #3

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

Expand|Select|Wrap|Line Numbers
  1. Sub sample1()
  2. Dim Tbl1 As DAO.Recordset
  3. Dim db As Database
  4. Set db = CurrentDb
  5. Set Tbl1 = db.OpenRecordset("A1 Movie Code Table",  dbOpenTable)
  6.  
  7. With Tbl1
  8.     .AddNew
  9.     !FirstName = [form control Value1?]
  10.     !LastName = [form control Value2?]
  11.  
  12.     '.
  13.     '.
  14.     'etc where [form control Valuex] is the value of
  15.     'whatever form/control you're referencing
  16.  
  17.     .Update
  18.     .MoveNext
  19.     .Close
  20. End With
  21. Set db = Nothing
  22. End Sub
  23.  
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?
Jan 17 '12 #4

100+
P: 144
Correction in the code I sent: remove the .movenext line. Sorry
Jan 17 '12 #5

100+
P: 144
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.
Jan 17 '12 #6

100+
P: 144
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.
Jan 17 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
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 ;-)
Jan 17 '12 #8

P: 34
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 DidEmployeeDownload 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:

Expand|Select|Wrap|Line Numbers
  1. ' This final procedure copies the data in the "FirstName", "LastName", "KnownAs", "Title" and "DidEmployeeDownload" form
  2. ' controls  to same named fields in "A1 Movie Code Table".
  3.  
  4. On Error GoTo PROC_ERR
  5.  
  6. Dim db As Database
  7. Dim rs As Recordset
  8.  
  9. Set db = CurrentDb
  10.  
  11. rs.Edit
  12.  
  13. rs![FirstName] = Forms.[A1 Onboarding Tracking Form].[FirstName].Value
  14. rs![LastName] = Forms.[A1 Onboarding Tracking Form].[LastName].Value
  15. rs![KnownAs] = Forms.[A1 Onboarding Tracking Form].[KnownAs].Value
  16. rs![Title] = Forms.[A1 Onboarding Tracking Form].[Title].Value
  17.  
  18. rs.Update
  19. rs.Close
  20.  
  21. Debug.Print ("Populate used movie code recipient info. to movie code table")
  22.  
  23. Set rs = Nothing
  24. Set db = Nothing
  25.  
  26. Exit Sub
  27.  
  28. 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"
  29.  
  30. Resume PROC_EXIT
  31.  
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?
Jan 17 '12 #9

NeoPa
Expert Mod 15k+
P: 31,494
@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.
Jan 17 '12 #10

P: 34
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
Jan 18 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 18 '12 #12

P: 34
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."...meaning 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.
Jan 18 '12 #13

100+
P: 144
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.OpenRecordset("YourTable", dbOpenTable) to use the line that follow. Are you just writing code or are you testing it as you go?
Jan 18 '12 #14

NeoPa
Expert Mod 15k+
P: 31,494
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).
Jan 18 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
It sounds like either A) you are trying to copy data from one table to another or B) you want to select the data for one table from the choices available in another.

In response to A, why store two copies of the same data? There's no reason to do that. In response to B, use a combo box.
Jan 18 '12 #16

P: 34
Hey, Everyone,

Thanks for giving the vb virgin's code hail-mary such attention. How and how much it is and will be appreciated you'll never know.

NeoPa,
It's good to know that I can make my form "movie code" control bound to a field and still be populated with data from another table. I think I should go the route of binding it to my main table.

Rabbit,
It's sort of B)...I need to have a movie code and a person's last name on the same record in the same table or have the movie code or last name on my two tables for query purposes. The problem arises in that I can only want to store the movie code & associated "used/unused" status in one table (a status field is needed so I can populate my form from my secondary table in the first place). So far, I've been seeing the problem as the need to "pivot" off populated data to add to table records that are associated with it. Now, I'm going to look at the route of creating a "movie code" field in my primary table, binding the "movie code" control to it as normal, seeing if it will populate from my secondary table that holds the codes, and forgetting about adding the name info. to my secondary table. The query I will build will match records based on the movie code. Yes, I'll have duplicate data in the two tables, but my primary table HAS to have the person's last name in it.

C CSR,
I can't use a drop-down menu because the population data will be expanding and changing status monthly and it seems like it would get messy fast.

Thank you,

Frank
Jan 18 '12 #17

Rabbit
Expert Mod 10K+
P: 12,366
Can you show us some sample data from both tables?
Jan 18 '12 #18

100+
P: 144
Soule:

I'm not trying to sell you a dropdown, but know this:

1) You can use a dropdown for dynamically changing lists. They change as the underlying table changes.

2) If your data is well thought out and properly grouped, you can use the same dropdown for multiple lists. Example:

a -- list say cars, planes, boats. Onclick, list now says
b -- gas, diesel, ethenol. Onclick, list now says
c -- red, green blue, purple. Onclick.....etc. on and on.

You don't have to use a dropbox; you can use a list.

Do you want to know how? Explain your data, in detail (i.e, 1), 2) 3),....)
Jan 18 '12 #19

100+
P: 144
I'm with Rabbit's last comment #18, and its past time to do this.

Your options are wide open. I appreciate the fact that you're actually trying to think through some of the suggections that have been made, but you are limiting yourself when you presuppose what you can't do. Give use some sample data, well listed and separated out--and don't leave any "columns" out. First we analyze, then we synthesize!

Rabbit:

Take the lead please. I'm not volunteering you, but the focus should be maintained and I don't mind being a secondary. I will fill in or make suggestions, but I will limit my comments to your topic. OK :)
Jan 18 '12 #20

Rabbit
Expert Mod 10K+
P: 12,366
Thanks CSR. Don't feel obligated to be restrained by my responses though. I usually opt for a terse response and your more detailed posts can only be beneficial to the original poster. And I don't mind different solutions being posted for the same question. IMO, the focus of a thread is the solution of a question as opposed to the implementation of a particular solution. I think multiple solutions can be helpful as well, it shows that there's more than one way to approach a problem.
Jan 18 '12 #21

P: 34
Hey, Guys,

I'm sorry I can't provide table and form views, because my stuff is proprietary to a large movie studio. I really wish I could. I'll give you as much as I can. I can say that I have:

- (1) Form
- (1) Primary table named "A1 Tracking Table"
- (1) Secondary table named "A1 Movie Code Table"

I altered the structure of my two tables so that the only field they have in common is "MovieCode". The "Movie Code:" control on my form is control sourced to the field on my "A1 Movie Code" table.

I'm keeping track of unused and used codes in the secondary table using a field called "MovieCodeSendDate" fed by a control on the form. However I end up populating the movie code control, this date form control will be data-entered by the user.

So, you're saying I could use a drop/combo/list box from which the user can pick from codes that haven't already been used (where the "MovieSendDate" field of that record in the secondary table is null)?

That does sound good to me, but would that box code be less/more/equal in complexity to the code I'm currently using, which I want(ed) to populate the "Movie Code" form control on a Lostfocus event procedure (i.e. I took it out of my automation button standard module and put it in its own):

Expand|Select|Wrap|Line Numbers
  1. Private Sub A1S1_Form_Movie_Code_Autofill_Control_LostFocus()
  2.  
  3. On Error GoTo PROC_ERR
  4.  
  5. Dim db As Database
  6. Dim rs As DAO.Recordset
  7.  
  8. Set db = CurrentDb
  9.  
  10. ' Open recordset and pick first movie code with no previous send date associated with it from top of list.
  11. Set rs = db.OpenRecordset("SELECT [A1S1 Movie Code Table].MovieCode FROM [A1S1 Movie Code Table] WHERE [MovieCodeSendDate] Is Null ORDER BY [MovieCode] ASC")
  12.  
  13. ' Populate movie code form control with code picked from movie code table.
  14. rs![MovieCode] = "Me.[MovieCode]"
  15.  
  16. Forms.[A1S1 Movie Code Table].MovieCode.Update
  17. rs.Close
  18.  
  19. Debug.Print ("Populate moviecode data from table to form control")
  20.  
  21. PROC_EXIT:
  22. Exit Sub
  23.  
  24. PROC_ERR:
  25. MsgBox "Error populating table next movie code to form control." & vbCrLf & "Check to see if there are unused codes in movie code table." & vbCrLf & Err.Number & " " & Err.Description, vbExclamation + vbOKOnly, "Populate Table Next Movie Code To Form Control"
  26.  
  27. Resume PROC_EXIT
Thanks so much, guys, for helping me on this thread.
Jan 19 '12 #22

100+
P: 144
Rabbit:

This just looks backwards. This is trying to put data in Table from Control, not from table into control.

Expand|Select|Wrap|Line Numbers
  1. ' Populate movie code form control with code picked from movie code table.
  2. rs![MovieCode] = "Me.[MovieCode]"
  3.  
  4.  
Jan 19 '12 #23

100+
P: 144
I've got to point out that even if Soule wants to put control data into table,...don't use quotes around Control Value. And, I guess brackets are used here as descriptive, but confusing in VB code because they are as valid as part of the code as using ().

Soule: Find another way to use placeholders instead of bracket, like ... or {...x...}. Make sense?
Jan 19 '12 #24

Rabbit
Expert Mod 10K+
P: 12,366
I don't understand what it is you're trying to do.

You have a form, whose record source is the movie code table. You have a control on that form that is linked to movie code on that movie code table. You have code that attempts to populate the movie code field in the movie code table with the value of the movie code control. (Which, by the way, is not what the comments say it does).

All the code, some of which is syntactically wrong, and some of which doesn't do what the comments say it does, makes little to no sense without any context.

That's not to mention that the code you have here is not the same as the code in the first post. Which means that either you've changed the code, or you're leaving out relevant information.

Start over and explain the business requirements, not the technical requirements. I don't want to see the words database, form, table, or field in your business requirements.
Jan 19 '12 #25

P: 34
I want to put data in a control from a different table field than the control is sourced to.

In my comment #17, I said I was going to take a new tack. I should have started another thread then. I'll do that now.
Jan 19 '12 #26

100+
P: 144
Its confusing why my answer # is chosen as best answer under the label of "Type Mismatch," or for that matter, as a comprehensive solution to the overall issue. I don't think we concluded. Oh well.
Jan 20 '12 #27

Rabbit
Expert Mod 10K+
P: 12,366
I'm uncertain as well. Perhaps they rethought the situation and ultimately concluded that a combo box or list box was what they needed.
Jan 20 '12 #28

NeoPa
Expert Mod 15k+
P: 31,494
I suspect Frank decided to put his focus into asking a different question which, from reading through this one he realised he needed, and didn't want to leave this one without reflecting that he had been answered (Enough at least to realise he was originally on a non-viable track). From there it was a question of deciding which post most helped him get that understanding I guess.

I think he showed good manners in a pretty complicated situation :-)
Jan 20 '12 #29

100+
P: 144
NeoPa & Rabbit:

I wrote a couple of "primers" for Soule but I never posted them because they were maybe too lengthy and I didn't want to sound condescending. The only thing that still bothers me about his programming attempts was that it was never clear whether he understood how to assign values from a table to a control, and vice-versa. I think he had that backwards, so his tests were failing and that's why his questions were hard to answer.

Sometimes I don't know whether to 1) just throw some code out and see what happens or 2) try to make sure where the level of understanding is before I make things worse on both ends. I'm for "let's start at the beginning" before contributing in a project type inquiry, especially with my own clients, because its always what their leaving out in the algorithm that gives birth to the frustration.

Not specific to this thread, but in general, instructors should really place a whole hell of a lot more emphasis on prep, analysis and presentation of such. And, it usually comes out of my pocket when I jump around and let the client off the hook.

Your observations of my own technique in this type of "web support" (to which I was never personally invited but do intend to improve my skills upon) would be most welcome, anytime. One example is, I may make a first response to a thread I think I may know something about by just asking a few basic questions, not knowing if I can eventually solve the coding issues--but hoping at least to lay some ground-work in case someone else has to pick it up when I have to bail. Is that being productive or misleading?

-- all for now. Soule: good luck.
Jan 20 '12 #30

Rabbit
Expert Mod 10K+
P: 12,366
I wouldn't worry about it too much CSR. I do that sometimes as well. When a question is vague, I'll test the water with a post and if it turns out to be a subject that I have no experience with, I'll pass it along to another expert.
Jan 22 '12 #31

P: 34
Ya'll were right on both counts. I decided to go with a calculated listbox and only 1 repeating field between two tables, thereby bidding adeiu to this thread and...

I discovered the joys of DLookup for myself. After reading the definition two months ago and seeing it pop up in code ever since, I see why so many people use it. :D

Thanks.
Jan 25 '12 #32

Post your reply

Sign in to post your reply or Sign up for a free account.