Thread: Chooser Form

bear with me river..... I've been extremely busy at work, and home has been crazy. I have looked at the chooser form briefly again, and I'll probably be doing if then statements on dcounts. First dcount will be on the tblskills table, if it isn't there (count <1)then open a form to add the skill. Second dcount will be on tblEmployeeSkills if count <1 then add it. Again, all of this will be on an afterupdate of an unbound textbox.

Ok, I should be able to get to this tonight after work (I'm at work now) So, I will be able to start on this in about 8 hours. I should have something up within 12 hours from now.

Heh, finished with 8 minutes to spare

ok, ok, so I put 1/2 hour into this.... but 1/2 hour from me is hard to come by these days.....

Ok, so all I did was put an unbound textbox, and then this code behind it:

Code:

Private Sub scanned_AfterUpdate()
Dim skillscount As Integer
Dim empsklscount As Integer
Dim EmptySkilldescr As String
Dim MySql1 As String
Dim MySql2 As String
Dim MySql3 As String


'Error check for too small of entry
If Len(Me.scanned) < 2 Then
MsgBox ("No Valid Code Entered")
Me.scanned = ""
Me.scanned.SetFocus
Exit Sub
End If


'count skills in tblskills and tblEmployeeSkills
skillscount = DCount("[skilldescription]", "tblskills", "[skillid]='" & Me.scanned & "'")
empsklscount = DCount("[employeeidfk]", "tblEmployeeSkills", "[employeeskill]='" & Me.scanned & "' And [employeeidfk]='" & Me.EmployeeID & "'")

'update tables as necessary
If skillscount < 1 Then
EmptySkilldescr = InputBox("Item not in listed skills. Please enter brief description")
MySql1 = "Insert Into tblskills (SkillID, SkillDescription) Values ('" & Forms!formmain.scanned & "','" & EmptySkilldescr & "');"
MySql2 = "Insert Into tblEmployeeSkills (EmployeeIDFK, EmployeeSkill) Values ('" & Forms!formmain.EmployeeID & "','" & Me.scanned & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL MySql1
DoCmd.RunSQL MySql2
DoCmd.SetWarnings True

ElseIf empsklscount < 1 Then

MySql2 = "Insert Into tblEmployeeSkills (EmployeeIDFK, EmployeeSkill) Values ('" & Forms!formmain.EmployeeID & "','" & Me.scanned & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL MySql2
DoCmd.SetWarnings True

End If

'delete if necessary
If empsklscount > 0 Then
MySql3 = "Delete * From tblEmployeeSkills Where [EmployeeIDFK] = '" & Forms!formmain.EmployeeID & "' and [EmployeeSkill] = '" & Me.scanned & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL MySql3
DoCmd.SetWarnings True
End If

'requery lists, delete value from textbox, and set focus to textbox
Me.List6.Requery
Me.List8.Requery
Me.scanned = ""
Me.scanned.SetFocus
End Sub

It looks worse than it is.

Step 1, after update of the textbox check the length.... if it's less than 2 characters long, then it does nothing and blanks out the textbox.

Step 2, I did a dcount on tblSkills (where the master set of skills are stored) and tblEmployeeSkills. If the count is less than 1 in either, that means the skill is not there and needs to be entered.

If it isn't in the master table (tblSkills) then it asks for a brief description, and adds it to both tables.

If it isn't in the employee skills table (tblEmployeeSkills) but is in tblSkills then it adds it to tblEmployeeSkills.

If the skill is in the skills list (tblSkills) and it is in the employee skills table (tblEmployeeSkills), then it removes it from the list of skills the employee has.

Finally it requeries everything and sets focus back onto the textbox for scanning and erases the previous entry.

This is only a rough draft and not fully tested. For one, I don't have a scanner. Anyhow, without further adieu, here is the updated version.

Now, you have a rough draft for what you need to do. You can refine the database to your needs. In the near future, I might even make this database include prices. Given the base program, you can do pretty much anything you have to. Keep me informed if you need added functionality. I'll be happy to do so, but give me time. I'm in the middle of redoing our company's web site, which consists of 10+ pages......

Edit - clean up code box a bit.

The most obvious thing to use here is list count,. although i don't know what it is with that function, but it seems to have issues

Anyway,

you won't be able to run this direct with ListName.ListCount, so add a text box to your form.

Control Source,. =List6.ListCount ' list 6 being that in sbenjs example for skills available.

Then Add, the following events- [Form OnCurrent],[List Box Dbl Clicks],[List Box Transfer Buttons OnClick], and [Form Navigation Buttons OnClick]

Code:

If Me.TextBoxName = 0 Then
Me.CommandButton.Enabled = True
Else
Me.CommandButton.Enabled = False
End If
Me.List6.Requery

End Sub

See attached,

Thanks

AOG