What should i use? Excel or Acces.

[Botje]

Hej there,

I'm on this forum for about 2 weeks now.
Busy with Excel Userform trying to get it done!
And thanks to the people here it looks lovely and it works..

But... I have a problem!

When 2 or more people simultaneously open my Useform, fill it in.
And then save it, it will give errors because the files are different because of the data that is filled in..

What i want to achieve is the following:
About 500 people should be able to fill in the form at all time without getting "can't save the file" errors.
​

I was wondering...

Did I make a mistake by using Excel?
Or should I make an form in Acces that automaticly imports the Data from Excel? ( If that is possible. )
Or should I make the form in Acces?
​

Thanks in advance for any tips/tricks etc! ( PS. Using Excel and Acces 2003 )

If it is possible I can add the .xls file if thats nice to have.
This is my entire code:

Private Sub checkAnders_Click()
If checkAnders.Value = True Then
TextAnders.Enabled = True
TextAnders.Visible = True
Else
TextAnders.Enabled = False
End If
End Sub
Private Sub UserForm_Activate()
TextAnders.Visible = False
End Sub
 
Private Sub cmdinvullen_Click()
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
    If Me.ComboBox1.Value = "" Then
    MsgBox "Vul alsjeblieft je team in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.ComboBox1.SetFocus
    Exit Sub
End If
    If Datum.Value = "" Then
    MsgBox "Vul alsjeblieft een datum in."
    Exit Sub
End If
    If Not IsDate(Datum.Value) Then
    MsgBox "Ongeldige datum ingevoerd."
    Exit Sub
End If
If DateValue(Datum.Value) <= Date Then
    MsgBox "Datum dient in de toekomst te liggen."
    Exit Sub
End If
Dim Resp As Variant
    Resp = MsgBox("Je hebt de volgende informatie ingevoerd, klopt dit? " & vbNewLine & "Naam  :        " & Me.TxtNaam.Value & vbNewLine & "Team  :        " & Me.ComboBox1.Value & vbNewLine & "Idee    :        " & Me.txtidee.Value, vbYesNo + vbQuestion)
    If Resp = vbNo Then
                    Exit Sub
    Else
        MsgBox "Idee ingevoerd!"
 
End If
RowCount = Worksheets("Archief").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 14).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
.Offset(RowCount, 15).Value = Me.ComboBox1.Value
.Offset(RowCount, 25).Value = Me.TextAnders.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 19).Value = "X"
Else
.Offset(RowCount, 19).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 20).Value = "X"
Else
.Offset(RowCount, 20).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 21).Value = "X"
Else
.Offset(RowCount, 21).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 22).Value = "X"
Else
.Offset(RowCount, 22).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 23).Value = "X"
Else
.Offset(RowCount, 23).Value = ""
End If
If Me.checkAnders.Value = True Then
.Offset(RowCount, 24).Value = "X"
Else
.Offset(RowCount, 24).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 1).Value = "X"
Else
.Offset(RowCount, 1).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 2).Value = "X"
Else
.Offset(RowCount, 2).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 3).Value = "X"
Else
.Offset(RowCount, 3).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 4).Value = "X"
Else
.Offset(RowCount, 4).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 5).Value = "X"
Else
.Offset(RowCount, 5).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 6).Value = "X"
Else
.Offset(RowCount, 6).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 7).Value = "X"
Else
.Offset(RowCount, 7).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 8).Value = "X"
Else
.Offset(RowCount, 8).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 9).Value = "X"
Else
.Offset(RowCount, 9).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 10).Value = "X"
Else
.Offset(RowCount, 10).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 11).Value = "X"
Else
.Offset(RowCount, 11).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 12).Value = "X"
Else
.Offset(RowCount, 12).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 13).Value = "X"
Else
.Offset(RowCount, 13).Value = ""
.Offset(RowCount, 16).Value = Format(Now, "dd/mm/yyyy hh:nn")
End If
.Offset(RowCount, 17).Value = DateValue(Datum.Text)
End With
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub
Private Sub UserForm_Click()
End Sub

 

[Botje]

Botje

If you set things up properly in Access you might not need code and you should definitely not jump to using code.

That's a mistake I've seen happen quite a bit.

You should concentrate on planning and designing the database.

Ok, going to do that then ty ( PS. Can't change my language @ my workspace to english.. )

 

[Botje]

Well now i got the feeling how i should build the database.. it looks good..

Now i gotta find out how to export the data from excel to Acces automaticly

Dont know if this is allowed but..

http://inpics.net/tutorials/access2003/basics.html

This is just a great tutorial for a new-comer! Shows all the basic stuff.

 

[Botje]

Botje

If you are going to use Access then you'll probably need to restructure your data.

You'll probably find out more about that when learning about databases.

There's actually a few things you should have a look at in the workbook/userform.

For example, the groups of checkboxes could be replaced with listboxes.

That would make things a bit easier when transferring the data to a worksheet.

It'll also make the code a lot shorter.

That's one of the things I noticed when I took a quick look at the file a few days ago.

I'll try and take a closer look today.

PS Nice bus.

You are right about the list boxes.. trying to fix them now!:D

 

[Norie]

What's the general structure you've decided on?

What tables etc?

Have you sketch it out?

I always find that's a good idea.

As for importing/exporting from Excel, what do you mean by 'automatically'?

 

[Norie]

Botje

I received the file and it looks good so far.

Couple of things though.

In Access don't use spaces or special characters in the field names, you can if you must but it can really cause problems.

Use short, but descriptive, field names and use the Caption property if you want to display something more descriptive.