Beginner needs Combo Box help

Beginner needs Combo Box help

Post by servicema » Thu, 09 Mar 2006 03:32:21


Hi all!
I'm a beginning Access 2003 user, and I'm having some trouble with combo
boxes. Here's my trouble:
I have a form where I want to edit or delete students from the table STUDENTS.
This table has an IDENTITY column (named IDENTITY), and it also has a
calculated column that concatenates FST_NAM(first name) and LST_NAM(last name)
; this column is named FULL_NAM(full name). This form also has a 'Delete
record' button. I have created a combobox(unbound) to look up records on the
form using FULL_NAM (Combo104).The problem is the way the combo box displays;
When I open the form the combo box text area is blank (this is ok), and the
list is populated correctly. When I select a record it displays fine, but if
I delete that record it stays in the text area of the combo box and in the
list as well. Through much trial and error I have gotten the list to update
by adding DoCmd.Requery "Combo104" to the code, but I cannot get the text
area to go to the next record, nor can I get the form to just clear
completely when I delete. Clearing the form and resetting the list would be
the preferred setup, but I'm L-O-S-T! I would also like to put a combo box on
the IDENTITY column that would function in the same way. ANY help would be
greatly appreciated!!! I would really like to get a better handle on Access,
as there are several daily tasks that I can make easier if I can just figure
it out!
Andy
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Thu, 09 Mar 2006 04:21:32

ound like you are pretty much on the right track. What you are experiencing
with the combo after the requery is normal. It is basically starting over,
so it will go to showing nothing in the combo box. As to the form, the
deleted record is deleted in the table, but is still in the recordset for the
form, so what you need to do is requery the form. This will also requery the
combo, so you could just use Me.Requery rather than the requery on the combo.
Now, what you will not like is that, as I stated previously, it is like
starting over, so the form will display the first record in the recordset
after the requery.

It is possible to make it go to the next record in the recordset (or at
least appear to the user to do so), but it takes some trickery and coding.
Below is a sub I use to do exactly that. I call it from the Click event of
my delete command button. I put it in a standard module so I can call it
from multiple forms. I call it like this:

Call DelCurrentRec(Me)


Public Sub DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Sub


"serviceman" wrote:


 
 
 

Beginner needs Combo Box help

Post by servicema » Thu, 09 Mar 2006 05:29:18

ey Klatuu!
Thanks for the quick response; Now all I need to do is get it to work!
In my bumbling coding attempts, I have put the DoCmd.Requery line in like
this:
(This is all the code for the form)
------------------------------------------------------------------------------
-------------
Option Compare Database

Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:

Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub



Private Sub Save_Changes__Click()
On Error GoTo Err_Save_Changes__Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Changes__Click:
Exit Sub

Err_Save_Changes__Click:
MsgBox Err.Description
Resume Exit_Save_Changes__Click

End Sub


Private Sub Command99_Click()
On Error GoTo Err_Command99_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command99_Click:
Exit Sub

Err_Command99_Click:
MsgBox Err.Description
Resume Exit_Command99_Click

End Sub
Private Sub Combo108_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[IDENTITY] = " & Str(Nz(Me![Combo108], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Requery
"Combo108"<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Here it is!
End Sub
Now, if I replace it with Me.Requery it doesn't work. Am I correct in
assuming that this Me.requery needs to go somewheres in the 'Delete Button'
code rather than the ComboBox code?
Andy

Klatuu wrote:
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Thu, 09 Mar 2006 05:47:30

t should be in the code of the click event of the delete command button.
Get away from using those menu bar command. They are impossible to read.
Use real VBA.

Also, I don't believe it will work with the code I sent. In this case, you
are actually deleting from the recordsetclone. I think you would end up
deleting either the record before it or after it depending on the position of
the record, because the code above is deleting the current record in the form.

"serviceman" wrote:

 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 02:20:34

Klatuu;
This site is A-W-E-S-O-M-E!! The best advice so far has been
"Use real VBA"...
Ok, so I went out and picked up a couple books on VBA, and have gotten
several things straightened out, including this combo box issue. Now, being
armed with new knowledge and feeling brave, I decided to try and 'hop up'
this combo box even more by setting it up so that when a user types in data
that is not in the list, it opens a form to add the record and then returns
to the original form. I am having an issue with DLookup in the following code:

--------------------------------------------------------------------------
Private Sub Combo108_NotInList(NewData As String, Response As Integer)
If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """"))
Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

It pops up the new form ok, but after I enter the data and return to the 1st
form I get an error that says 'Invalid column name Sally Ride' where 'Sally
Ride' is the Student I just entered. This is the value of NewData, but why is
it seeing this data as a column name? I assume I am missing quote marks or
something, but I just can't get it...
Andy


--
Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Fri, 10 Mar 2006 03:02:59

ood for You! Hoorah!
Your syntax is just a little off. Getting the right number of quotes, to
me, is the hardest thing to do correctly. I am ashamed to say I still have
to play with it until I get it right. What I do is go to the immediate
window and work on it. For example, in this case I typed in
Newdata = "Sally Ride"
Then tried several different combinations until I found one that worked. It
is this:

"FULL_NAM = """ & NewData & """

So the full command is:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """))

One other thing that will help you is using indentation in your coding. It
makes it so much easier to read. Here is how I would structure your code:

Private Sub Combo108_NotInList(NewData As String, Response As Integer)

If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, _
acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ _
& NewData """)) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub


"serviceman via AccessMonster.com" wrote:

 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 03:22:59

Klatuu!
Almost, but not quite! I copied and pasted your new code, and I get two
errors:
1st, the _ in the dlookup line causes an 'invalid character' error.
2nd, with one less quote mark I get a syntax error.
BUT!!! I just thought of something; could this be because I left out the very
important point that this machine is running SQL Server, and that is where
the DB is? Does this have anything to do with how this gets
commented/quoted/bracketed?
I'm getting closer, I can feel the love!
Andy



--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Fri, 10 Mar 2006 03:47:29

he problem you had with the _ is that it is a continuation marker, that is
it is always at the end of the line of code and tells the compile "This
statement is continued on the next line." I put it in there so I could show
formatting technique.

That should solve the problem with the query. If not, I am not familiar
enough with using SQLServer to know if a syntax difference exsits for this
situation. Being it is a DLookup, it should not make a difference. Try
this. As I said earlier, go to the immediate window. type in : Newdata =
"Sally Ride" Then type in:
X = "FULL_NAM = """ & NewData & """
Then type in ?X
What you should see returned is
FULL_NAM = "Sally Ride"
That means the syntax(number of quotes) is correct. If you get something
else, try changing the quotes.



"serviceman via AccessMonster.com" wrote:

 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 04:24:07

In between my post and your reply I figured out the continuation marker
thing; one problem out of the way. Following your helpful insight, I
determined that the correct quotes are """ & NewData & """".
That is the second thing out of the way, but it still returns the same
'Invalid column Sally Ride' error.
NOW,
I got to thinking about this; and I think it may be my underlying strucutre
that is screwing things up. This combo box data uses this query:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME
from STUDENTS
The combo box format hides the IDENTITY column so users only see "Sally Ride"
When invalid data is entered, We open the form STUDENTENTRY to enter the new
student.
the column IDENTITY is (autonumber), FST_NAM is 'Sally', LST_NAM is 'Ride',
CALCULATED COLUMN FULL_NAM is concatenation of both.
When I close the form STUDENTENTRY, I get the 'Invalid column name' error.
Can it be that the DLookup query is ok but it is passing it back to the
combobox as the second column, with FULL_NAM as the first column? Does this
make sense? It seems to me that the query would need to return 2 columns to
the STUDENTMAINT form, not 1....
But hey, what do I know?? ;)

Andy



--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Fri, 10 Mar 2006 04:52:32

irst, the + sign is only for math. When concatenating strings use &, so
change:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME from STUDENTS
To
SELECT IDENTITY, FST_NAM & ' ' & LST_NAM as FULLNAME from STUDENTS;

Then I don't know what your bound column is in your combo, but for this
case, it should be 1, which would be the IDENTITY column. Now, assuming that
is a unique primary key, this would be better:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
& "))

The above assumes IDENTITY is a numeric field.

If you refer to Me.MyComboBox it returns the bound column of the currently
selected row. If you want to get data from other columns, the index will
start with 0 and you can get it like this:
Me.MyComboBox.Column(0) - In your case will return the IDENTITY
Me.MyComboBox.Column(1) - In your case will return the FULLNAME
Not those literals, but their values, for example if Sally Ride was the
current row, then Me.MyComboBox.Column(1) would return Sally Ride.



"serviceman via AccessMonster.com" wrote:

 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 05:13:36

'Round and 'round we go!
I am so loving this!
The '+' sign thing is an SQL Server issue; '&' is used in MSAccess DBs, where
'+' is used in SQL Db's. (I think I am saying this right; even if I go to
table creation in Access it wants '+' signs...
Anyway---
I believe your new method is the way to go; I tried it, and now (Surprise!)
there is a new error:
Runtime 170
Line 1 Incorrect syntax near ')'
If I don't debug and just 'end' I then get the generic 'Text not found'
message for the combobox as it is not passing a requery back for the box. I
went to the immediate window (I'm learning!) and I see that Me.Combo108=118
and IDENTITY=118, so the Dlookup should work if we/you can fix the syntax
error...
Closer, Closer......
Andy



--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 05:27:38

CLOSER AND CLOSER!!!
This almost works:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.Combo108))
Then

This passes the name back to the combo box in STUDENTMAINT without an error!!
Now I just need to get the Form to refresh with the new students info and
this will be good to go!
Since we're having so much fun, any idea how to split NewData into separate
FST_NAM and LST_NAM values so I can pass it into the STUDENTENTRY form? What
the heck, I'll just build a monster!

Andy



--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by serviceman » Fri, 10 Mar 2006 05:29:19

Spoke Too soon...
It Doesn't work; the incorrect syntax is still there...



--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com
http://www.yqcomputer.com/
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Fri, 10 Mar 2006 05:34:27

I told you I was week with SQLServer SQL syntax, so maybe the + is the
correct way to do it.

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
))

Ooops!, perhaps I should read what I wrote before I post, I had the syntax
wrong for the combo box in the previous post. I think the above will work.
Please let me know when you get this working.
 
 
 

Beginner needs Combo Box help

Post by S2xhdHV » Fri, 10 Mar 2006 05:53:26

Are we working in two different forms here? I guess I missed that before.
The syntax should be correct. I can't see a problem with it. Is the code in
the same form as the combo box? Post back with the complete procedure and
let me look at it. As to the NewData, I think it will have the Identity in
it rather than the full_name, but I never use it, so I am not really
positive. What I do know, is that column(1) of your combo box will have it.
And, when you ask if you could split it, you gave the answer. Use the Split
function.

Dim varBothNames as Variant

varBothNames = Split(Me.MyCombo.Column(1), " ")

Based on full_name being first & " " & last, there will always be a space
between them. So,
varBothNames(0) will = Sally
varBothNames(1) will = Ride

Now for the gottcha. If you have either or both of the names that have
spaces in them, you will get a different result. The Split function
separates what you want to split (1st argument) on the character specified in
the second argument.