Access combo box - to not allow a period 2

[Eprice]

Hi,
I am creating a form in access with a combo box that is not 'Limit to List' but I don't want the user to be able to enter a period, e.g. 'NT 4.0', because what they enter is going to become a new table field name which does not allow periods. If it is possible, how do I restrict them from entering a period.
Thanks, Lisa

 

[dhookom]

You should be able to use the KeyPress event like:

Private Sub cboYourCombo_KeyPress(KeyAscii As Integer)
    If KeyAscii = Asc(".") Then
        KeyAscii = 0
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

what they [users] enter is going to become a new table field name

Do you mean if they enter "That's My Field? Hurray!!!" you will add a new field with that name to your table?

If that's the case, it is a very bad idea.... IMHO

Have fun.

---- Andy

 

[Eprice]

Thank you Duane that worked.
I am making a database that tracks company software and licensing. In order to add new software (without them opening the tables) I am building a form that they use a text box to enter new software names into the table.

Why is it a bad idea to let them enter the name in the field?

Problem is that I can't get it to work as I would like. Using this code gives me 'strName' as the name of the field instead of what they enter into the text box.

Public Sub AddFields()
Dim strName
strName = Forms![frmNewSWPurchase].PickSoftware & " - " & Forms![frmNewSWPurchase].txtVersion
Dim dbsNew As DAO.Database
Set dbsNew = DBEngine.Workspaces(0).OpenDatabase("S:\Access\Operations\Inventory\DishData.mdb")
dbsNew.Execute ("ALTER TABLE tblUserSoftware ADD COLUMN strName Text")
End Sub

Do you know how I should write this?
Lisa

 

[PWise]

This is going from bad to worse you should not have to add a new field for each new software.

What you should have is a softwear table and add a new record to that table

 

[Eprice]

I do have a table that holds the software for licensing counts (entered from a form normally as should be). I need to be able to show what employee has what software. We have a LOT of employees but not much software so was going to use an employee table to track the software they use. When a new software is entered it will also add another field to the employee table to be marked if they have it. Got any better ideas? I am not an expert.
Lisa

 

[PWise]

You have an employee Table
You have an software Table
What you need is an EmployeeSoftware Table

Employee Table
Employeeid
EmployeeName
.....

Software Table
Softwareid
SoftwareName
....

EmployeeSoftwareTable
EmployeeSoftwareid
Employeeid
Softwareid

 

[dhookom]

A star for PWise for providing great advice. The EmployeeSoftwareTable is often referred to as a "Junction" table. There would be one record per employee per software.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

To answer your other question:

("S:\Access\Operations\Inventory\DishData.mdb")
dbsNew.Execute ("ALTER TABLE tblUserSoftware ADD COLUMN [blue]" & strName & "[/blue] Text")
End Sub

But don't do it this way

Have fun.

---- Andy

 

[Gammachaser]

I think Dr. Codd just rolled over in his grave.

"The key, the whole key, and nothing but the key, so help me Codd."

 

[Eprice]

Hi,
The thing is, doing it with a table with one record per employee per software is going to be a huge table. I had already thougt of that. We have over 500 employees with about 10 software each, some employees more. There is also a table for hardware for each employee so I was trying to keep the size down. I thought that by haveing one reocrod per employee with a column for each software would take less space. Thanks for your help and advise. Like I said I am not an expert... yet.
Lisa

 

[PWise]

Duane:

Thanks!!

 

[dhookom]

As fellow MVP John Vinson states "fields are expense, records are cheap". IMO, I wouldn't go any farther without normalizing the table structure.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

I have already set up the user software table with only two fields. Userno and SoftwareNo. The employee table is already connected to the hardware table by Userno so I am using that same number to connect the software table. I was just trying to save space. Thanks, Lisa

 

[dhookom]

Space isn't as important as having to maintain so many fields that need to be added to tables and queries as well as displayed on forms and reports.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

True, it would have made it much harder to cleanup old stuff and add new. Thanks

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886