Use String to change form control values

[DerekAwesome]

This is a stupid, low level question but nevertheless I want to know if it is possible.

Right now I'm creating a form with around 60 boxes, where you can click on 1 box, drag to another and release, and the second box's color will change to that of the first box. I already have the drag and drop code in place, but I was wondering if it was possible to use an array as the name of the control so I wouldn't have to make individual code for each box.

My drag and drop code is like this:

Private Sub BoxAdd_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
UpdateMouse "BoxAdd", Button
End Sub



Sub UpdateMouse(Name As String, Button As Integer)
If LastState = 0 And Button = 1 Then
Dragged = Name
End If

If LastState = 1 And Button = 0 Then
Dropped = Name
If Dragged = "BoxAdd" Then


Which works fine, but then I have 60 if statements that look like this:
If Dropped = "M1" Then
M1.BackColor = BoxAdd.BackColor

Is there a way I could use 1 single string to assign the color (instead of M1.BackColor assign M1 to some StringName and say StringName.BackColor) or even use an array for it?

Sorry for the confusing question, ask if clarification will help

[alansidman]

Changing the subject, but when I hear of a db with 60 controls (textboxes), I wonder if there is a normalization issue. Why do you have so many? What types of data are you storing that you need to have it spread amongst 60 fields? Are you familiar with data normalization within RDBMS.

Alan

[ssanfu]

I couldn't get your code to execute, but I did have an idea.
Since you know the "from" (dragged) control name and somehow you get the "to" (dropped) control name, you might try:

Sub UpdateMouse(Name As String, Button As Integer)
If LastState = 0 And Button = 1 Then
Dragged = Name
End If

If LastState = 1 And Button = 0 Then
Dropped = Name
Me(dropped).BackColor = Me(Dragged).BackColor



No need for an array or 60 statements...

[DerekAwesome]

I'm vaguely familiar with normalization but unsure of how to implement it well.

Basically, the goal of this project is to develop a weekly work schedule where you can select a worker at the top, then drag and drop them into time slots below. The 60 boxes are for each hour time slot (monday thru saturday, 10hr days), and it's the only method I currently know of to assign the work schedule visually as per my professor's request. If you have any suggestions on a better way to go about doing this or where I should go from here please let me know.

This is my first (and only required) semester of database, but seeing as I'm enjoying it I'd like to expand my knowledge past this class.
Basically my professor is incompetent and can barely teach the subject (let alone speak english). He assigns ridiculously difficult projects in hopes that one of his students will discover something new and he can make his next semester students do it. Most of the knowledge I have of access I've learned on my own.

[DerekAwesome]

oh and sorry for the double post, but ssanfu I put that code in and it worked beautifully, thank you.

I would still like to know a better strategy, if anyone wants to take the time to explain or send me a link where I can learn a little on the subject.

[ssanfu]

Great.
I haven't worked with drag and drop at all. Any chance you would post your dB so I could also learn (see what you did)??

Google "Normalization"

Some sites:
------------
Fundamentals of Relational Database Design
http://www.deeptraining.com/litwin/d...aseDesign.aspx

Access Basics By Crystal
http://www.accessmvp.com/Strive4Peace/

Also look at:
http://office.microsoft.com/en-us/ac...001213954.aspx

[alansidman]

I am also interested in this drag and drop concept as I have not seen or heard of it before.

Look at the links Steve sent as they will be helpful if you are looking to expand your database skills.

Remember "Google is my friend and can be yours as well."

[DerekAwesome]

Thanks for the links.

As for the database, here's one I used drag and drop in. Drag one number to another to switch them. There's no visual with it but it works,and the code is pretty simple

http://www.mediafire.com/download.php?idk6fyjkcvcyzc3

[ChrisO]

I’ve done a fair bit of drag and drop in Access and most of it is on my SkyDrive in the signature below.

The basics of what I use comes from a Russian site:-
http://am.rusimport.ru/MSAccess/topic.aspx?ID=413
look for Dragging.zip

Over the years I’ve tried to remove some of the complexity by re-writing the code to break it down into comparatively simple steps.

On my SkyDrive the steps start as Drag Drop -> Drag Drop Resize -> Drag Drop Resize GridSnap -> Drag Drop Resize GridSnap Save.

Because of the complexity I would recommend starting at Drag Drop.

Some applications of Drag and Drop may be seen as Drag Floor Plan, Drag Polygons, Z Order Bound Objects and Drag and Drop Chess.

They are all in Access 2003 (2000 file format) and totally late bound including DAO. That means no references at all, except the two which can’t be removed. They should also be immune to regional settings.

All that should at least keep your professor happy if only for the next semester.

Chris.

[DerekAwesome]

ChrisO that's pretty awesome, way more developed than the drag and drop I'm using. If you don't mind me asking how long did it take you to learn how to implement that?

[orange]

Derek,
Based on your comment on Normalization in post #4, here are some notes and videos that should help put things into context.
Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

A standard list of helpful items for new developers:
A good data model is the first step to realizing any database and Database Answers (free models) has several free models on which to base a business concept for getting some ideas to match what may closely compliment your business model.

The concepts of database building are best conceived when thoroughly planned . . .

• Database planning tutorial Part I
• Database planning tutorial Part II
• Argeedblu's FAQ on Design Strategies

And the following links on normalization cannot be stressed enough . . .

• Noah's FAQ on Normalization
• Roger's Blog on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• MSKB 283878: Description of the database normalization basics

Tutorials, KB Articles and Demos that can assist a new Access developer are found here:

• UA Access Tutorials
• Crystal's Access Basics for Programming
• Access Tutorial
• Access VBA Tutorial
• UA Access Knowledgebase / FAQ
• UA Code Archive

Good luck with your project

[ChrisO]

Maybe 6 or 7 years but I would like to stress the point that learn it is not past tense, I still regard it as work in progress.

Chris.

[Rod]

Thanks for that Chris. The only drag 'n' drop I have done is in the Treeview ActiveX control - and that was done 'by feel' and took a long time to implement.

[DerekAwesome]

Thanks for the help from everyone here. Was able to get my project completed, and I'm proud of it, although I understand it isn't very proffessionally done.

My goal for the project was to use colored boxes to record scheduling data instead of textboxes (mostly to see if I could), which means most of the data goes through the coding instead of queries ( which I still have around 70 of). I didn't have enoguh time to program all of the design features (like getting the drag and drop to be more user-friendly), so it may seem a little broken.

here's the link if anyone has time to look at it:
http://www.mediafire.com/?wtp914pspkvashg

If anyone wanted to make comments on my method of doing anything (design-wise, coding-wise or otherwise) and explain how I could have gone about it more cleanly and proffessionally, please feel free to comment. I'm really hoping to get better at access (and programming as a whole, trying to extend into different languages).

[ChrisO]

I wouldn’t mind having a look at it but would need a copy in Access 2003 file format.

Chris.