Replies

In a sense yes. The problem I ran into earlier was that I couldn’t find a way to store the states a given record would have so that when I recalled the record it would show what states that person chose. I had asked if there was an easier way to do it earlier but didn’t get a whole lot of help, thus causing me to develop what I did so that it would work. Of course I didn’t think about the reports. So if I can find out how to just convert those numbers somehow, or if i can figure out an alternative way to do it so the record remembers what states were chosen, and then put it all in a report that would be great. I am not exactly a veteran with access, I am rather new. Thanks for the help

Well see I started out doing it like that but I was having problems with a few things. Let me clarify kind of what led me to do what I did.

I had to create a data base that stored a bunch of different information. Now the user has to be able to select a multiple number of states for each individual record being entered. The record that is being entered could have a possibility of 56 things to be chosen. They are allowed to chose more than one since it is where the person is interested in going. I had problems with creating a table that had a State ID and then the list of options, a table with all relevant user info, and then a table between with UserID and State ID to create a one -to – many relationship. I was having problems with the recalling of records showing the states that a person would chose. So I developed code that would extract what was clicked on and save it into a text box and then put it into the relevant field in the Users table. Now I am trying to figure out how I can extract that data and reconvert it back into the state names. I hope this helps at all.

Thanks for the advice once again Hans, but unfortunately I am not using a query with this form. Here is what I did to get it to work though. This is the code I used.

Private Sub txtOpenDate_LostFocus()

txtCalDate = txtOpenDate + 90

Dim nowDate As Date
nowDate = Date
If nowDate > txtCalDate Then
chkFileClosed.Value = Checked
End If

End Sub

http://support.microsoft.com/search/previe…b;en-us;Q210203

that is the link to the article.

I have been trying to do that for a very long time, that however, just does not seem to work. I looked it up on microsofts website and they said it can be done with a little bit of code, but it is a bad design habit to get into since you won’t be able to query specific data from that field. For example if you have Apples, Oranges, Pears in the field cause you chose those three you won’t be able to look for just apples. At least that is the way I understood it from the article Microsoft putt out.

I thank you for the example. I think I will decide to go with a list box. My supervisor gave me the permission to do so just at the end of the day yesturday your example will help me out a lot in determining what I need to do. Now for a different question. Will the format you used work just as well for me if I want to be able to enter in new data into the tables? Also for the recalling of the record, these will be two different forms. So when ever I hit the new entry button a form comes up with blank fields in all the areas, and if I hit Update Record it runs a search by the persons ID number. After the search the another form is supposed to pop up with all the relevant data. Any suggestions on a different approuch or advice on that?

It would be neat to create a map, and not to difficult to do so. But unfortunately I don’t have the time or flexability to do all of that before the dead line. You also hit the nose on having to group the states. They are grouped by specific Zones, each state is in a certain zone.

hansV, – This is the setup I have so far. First I have a table that holds for the most part all the important user data: Name, Current zone, Current State, type of transfer wanted, Date file is recieved, date sent for review, amoungst other things. I also have a table that holds the fields for each of the 55 choices. One field for each choice with the Yes/No data type. One problem I’ve run into when creating my forms is somewhat simple, but difficult. For the purpose of design and looks I put the state selections on a different form so that it doesn’t clutter the other form. Now is there a way I can do this so that the state form will recognize the name that was entered on the original for so that if I need to I can print a report that will list the names of the people that want to go to a specific state. Thanks

Well normally that would be a good idea. The only thing is there are about 55 choices that can be chosen from. A person can chose 1 or 55 of the options. Now they wouldn’t click all 55 boxes if they would go anywhere because there is an ALL option. The combo box limits the number of choices a person can make and I am unable to do that since my requirements specify that I must give a person the option to chose as many as they want. If you can do that with combo boxes or in another way that isn’t so long then I’d appreciate the help.

hmm…yes that does seem a better way of handling it. Well thanks for the input of ideas. I was just curios as to what exactly was needed to be done to do it since I have never done something like that before. Course I’m new to VB as well so anything I learn is pretty new to me.

I think it’s your machine because I can open it and see everything in it fine. I will try what you suggested, and post back.

What are some of the many other ways? It would be good to have a variety for this kind of thing because from my experience in programming, well sometimes things don’t exactly work like they are supposed to.

Charlotte I thank you for all of your help, and I believe I found and fixed my problem. What was happening was that I had set the DataField property on my bound controls so it was always reading it into the first field, and the just updating it as neccessary or something along those lines. So by binding them to the table only and keeping the Datafield blank I was able to use code to get it to work the way I wanted it to. Here is my final code for that part of the Routine.

Private Sub MoveToRecord(intDirection As String)

Dim errCurrent As ADODB.Error

On Error GoTo MoveToRecord_Err

Select Case intDirection

‘Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.AddNew “SOID”, txtAlias
m_recLogin.Update “Password”, txtPassword2
m_recLogin.MoveLast

End Select

I’ll try to explain this the best I can. This is what happends when I use this code in my routine.

Select Case intDirection

‘Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.AddNew “SOID”, txtAlias
m_recLogin.MoveNext

Now I run the program enter in the information the table shows only “SOID” added. Good thats what it’s supposed to do. Now I go through it again and enter different information. this is where it gets hard to explain. It writes over the original entry putting in the information I entered. However, it also enters the password of the second entry. But it’s weird. Maybe I can sort of draw it out.

Program has run once. Entered: hhhh (user) gggg(pass)

Resulting Table:

SOID Password
hhhh

Run second time. Entered: nnnn(user) cccc(pass)

Resulting Table

SOID Password
nnnn cccc
nnnn

If you can explain why it does that when I use the AddNew then I would be appreciative.

Sure I understand that. However, when I use the AddNew function it keeps poping up an error

-2147467259 – Field Login.Password cannot be a zero-length string.

The code for that part looked the same as the Updates, except that it said AddNew. Update doesn’t give me any kind of error, where as this one does. It will write fine with the “SOID” one and not give any errors but the moment I add the Password it starts giving me errors. I don’t see why. I enter text in that text box, so why would it come back saying it is a zero-length string? Either way though even when I used it this way it just wrote over the existing information in that table. I add a MoveNext Function at the end and it still does the same thing.

Well I actually got rid of all my errors. Now the only problem I have is that when I enter the New User and Password it writes to the database perfectly. Problem is that it never moves down to the next field when I run it again. It just writes over what was entered the last time. Thats not good. Here is my code as of now. No errors are present.

Private m_recLogin As ADODB.Recordset
Private mcnn As ADODB.Connection

Private Const UPDATE_CANCELLED As Long = -2147217842
Private Const ERRORS_OCCURRED As Long = -2147217887

Private Sub cmdOK_Click()

‘Validate Passwords
‘Makes sure the password in both boxes are the same
If txtPassword2.Text = txtPassword1.Text Then
MoveToRecord (adRsnAddNew)
Else
MsgBox “Invalid Password.”, vbExclamation + vbOKOnly
txtPassword1.SetFocus
End If

End Sub

Private Sub Form_Load()

txtAlias = “”
txtPassword1 = “”
txtPassword2 = “”
Set m_recLogin = datLogin.Recordset
Set mcnn = m_recLogin.ActiveConnection

End Sub

Private Sub MoveToRecord(intDirection As String)

Dim errCurrent As ADODB.Error

On Error GoTo MoveToRecord_Err

Select Case intDirection

‘Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.Update “SOID”, txtAlias
m_recLogin.Update “Password”, txtPassword2

End Select

MoveToRecord_Exit:
frmTotals.Show
Exit Sub

MoveToRecord_Err:
If mcnn.Errors.Count > 0 Then
For Each errCurrent In mcnn.Errors
MsgBox errCurrent.Number & “–” & _
errCurrent.Description
Next errCurrent
mcnn.Errors.Clear
Else
Select Case Err.Number
Case UPDATE_CANCELLED, ERRORS_OCCURRED
‘Do Nothing
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
End If
Resume MoveToRecord_Exit

End Sub

Thanks for any help