Type Mismatch

[Rabbit]

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.

[soule]

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

[Rabbit]

Can you show us some sample data from both tables?

[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),....)

[C CSR]

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 :)

[Rabbit]

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.

[soule]

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 "MovieCodeSendD ate" 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 "MovieSendD ate" 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):

Private Sub A1S1_Form_Movie_Code_Autofill_Control_LostFocus()

On Error GoTo PROC_ERR

Dim db As Database
Dim rs As DAO.Recordset
  
Set db = CurrentDb

' Open recordset and pick first movie code with no previous send date associated with it from top of list.
Set rs = db.OpenRecordset("SELECT [A1S1 Movie Code Table].MovieCode FROM [A1S1 Movie Code Table] WHERE [MovieCodeSendDate] Is Null ORDER BY [MovieCode] ASC")

' Populate movie code form control with code picked from movie code table.
rs![MovieCode] = "Me.[MovieCode]"

Forms.[A1S1 Movie Code Table].MovieCode.Update
rs.Close

Debug.Print ("Populate moviecode data from table to form control")

PROC_EXIT:
Exit Sub

PROC_ERR:
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"
  
Resume PROC_EXIT

Thanks so much, guys, for helping me on this thread.

[C CSR]

Rabbit:

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

' Populate movie code form control with code picked from movie code table.
rs![MovieCode] = "Me.[MovieCode]"

[C CSR]

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?

[Rabbit]

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.

[soule]

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.

[C CSR]

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.

[Rabbit]

I'm uncertain as well. Perhaps they rethought the situation and ultimately concluded that a combo box or list box was what they needed.

[NeoPa]

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 :-)

[C CSR]

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.