Creating a new table

I'm not sure if this can be done, i was wondering can you create a new table with all the fields to save new information when you are in a form, if it can be done can anybody help me

Hali,

You can create a table from VBA code in many ways, I include here two options: SQL and DAO.

SQL Example:

Dim MySQL As String
MySQL="select Field1, Field2 from Table into NewTable"
DoCmd.RunSQL MySQL

Field1, Field2, Table and NewTable are just sample names that can be your data on the form and the tables you wish to retrieve the ata from and the table you wish to be created.

DAO Example:
Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim RS As DAO.Recordset
Set DB = CurrentDB
Set TD = DB.CreateTableDef([table_name]) 'create a table
TD.Fields.Append TD.CreateField([field_1_name], [field_type], [field_size]) 'create fields
Set TD = DB.CreateTableDef([table_name]) 'create a table
TD.Fields.Append TD.CreateField([field_2_name], [field_type], [field_size])
.
.
.
Set TD = DB.CreateTableDef([table_name]) 'create a table
TD.Fields.Append TD.CreateField([field_n_name], [field_type], [field_size])


'When all the fields added, then you can append the new record:
Set RS = DB.OpenRecordset([table_name])
With RS
.AddNew
!Field_1 = [value_1]
!Field_2 = [value_2]
.
.
.
!Field_n = [value_n]
.Update
.Close
End With

If you wish to add the record stored in a form that BOUND to a table, then you can use both, but you should include a WHERE clause to your SQL statement to specify which record from the table should be inserted into the new table.

If you wish to add the record stored in an UNBOUND form, then I recommend to use DAO.

For more, check out topics like DAO, TableDef, TableDefs in Help.

Hali,

Sorry, I made a copy-paste mistake. DAO Example correctly:
Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim RS As DAO.Recordset
Set DB = CurrentDB
Set TD = DB.CreateTableDef([table_name]) 'create a table
TD.Fields.Append TD.CreateField([field_1_name], [field_type], [field_size]) 'create fields
TD.Fields.Append TD.CreateField([field_2_name], [field_type], [field_size])
.
.
.
TD.Fields.Append TD.CreateField([field_n_name], [field_type], [field_size])

The rest of it is OK.

thank you for the code, but i'm not sure if what i'm doing is the best way what do you think, as i said before i'm creating a database to contain IP Address, and there quite a lot of them e.i.

126.10.17.01, 126.10.17.02, 126.10.17.03
126.10.18.01 , 126.10.18.02, 126.10.18.03
etc

The user would be able to add new IP address, so i put each set of IP address in their own table, i was wondering is that a good way to put it on a database

if you were creating this database how would you do it, and how would you look up each ip address

darrencftse,

If each user should have separate table to add the new IP addresses to, then I would create a new table when a user logs on to the database for the first time.

The [table_name] argument would look like something like this:

"tblIP" & CurrentUser

Using this code, if the user name is GMichael, for example, then the table name would be tblIPGMichael.

Each time a user logs on I would check if there is an existing table for the user to avoid overwriting the existing user table.

Anyway, what is the reason using separate table for each user?

I would rather use one table and indicate the user in a field. This field would tell you which user added the record.

I hope this makes sense.

Sorry i must have wrote my message wrong.
All users can use the database, what ive done is have a new table for each set of IP address i.e

a table will contain IP Address
126.10.17.01
126.10.17.02
126.10.17.03
126.10.17.04
126.10.17.05
126.10.17.06
etc

Another table for another set of IP Address, I was wondering is that a right way of doing it

I want to attach a sample of the database but the file seems to be too big to be attached to this message, is there a way i can send you a copy so you can take a look and check if what im doing is correct

Compress and zip the database and either upload or send it to:

tbarpad@axelero.hu

mailbox limit is 5MB.

Darren,

I apologize for not reacting as I promised.

Here is my opinion:

As I understood, IP Addresses are grouped by their first three part. If a user selects a set of IP Address by selecting one of the radion buttons on your form, then the appropriate combo box will be visible and the user can select the IP Address. These combo boxes are currently working from three different tables.
If a user enters an IP Address that does not belong to any set of IP Addresses, then you wish to create a new table and add the new IP Address there. Is this correct?

I think if you do that, then you should create a new radio button for the new set, right? And also a new combo, and all this should be done from code because users are not supposed to be waiting for the developer who puts a new radio button and a new combo onto the form in design view.
Of course, adding controls from the VB code is possible, but what if you have 20 sets of IP Address? Then you create 20 radio buttons, and 20 combo boxes?

So you were absolutely right wondering this is the right way.

If I were charged with building this form, then I would store all IP Addresses in a single table. I would use a combo box for selecting the set, and an other one which would list the IP Addresses belonging to the selected set. Both combo boxes would get their row source from this table.

1. The row source of the first combo would be fixed to this:
'*********************
SELECT Left([IPAddress],InStrRev([IPAddress],".",-1,1)-1) AS IPAddSet
FROM IPAddress
GROUP BY Left([IPAddress],InStrRev([IPAddress],".",-1,1)-1)
ORDER BY Left([IPAddress],InStrRev([IPAddress],".",-1,1)-1)
'*********************
This would now return three records:
126.0.100
126.0.80
126.0.90

2. Then I would refresh the second combo when the user selects a set from the first combo, so I would insert the following code into the AftreChange event handler of the first combo:
'*********************
Combo2.RowSource="select IPAddress from IPAddress where Left([IPAddress],InStrRev([IPAddress],".",-1,1)-1)='" & Combo1 & "'"
Combo2.Requery
'*********************
(Note: AfterChange event occurs only if the user uses the mouse to select a value from the combo box. I don't know why, but this is which I experienced. You can use OnChange instead.)
This code makes sure that only those IP Addresses are shown in the second combo box that belong to the set selected in the first combo.

3. To make sure that the first combo is refreshed when a new IP Address added by the user, I would insert the following code into the NotInList event handler of the second combo:
'*********************
Combo1.Requery
'*********************

So that if a user adds a new IP Address "126.0.200.01" which doesn't belong to an existing set, then the first combo would return four records:
126.0.100
126.0.200
126.0.80
126.0.90

I hope this helps you.

Darren,

Sorry, I forgot that you need to filter for the IP Addresses that are not taken yet.

1. To put the free IP Addresses in the second combo box use the following code in the AfterChange event handler of the first combo box:
‘*********************
Combo2.RowSource="select IPAddress from IPAddress where Left([IPAddress],InStrRev([IPAddress],’.’,-1,1)-1)='" & Combo1 & "' and Taken = False"
Combo2.Requery
‘*********************

2. When all IP Addresses from a set are taken, then that set should not appear in the first combo. To make so, use the following SQL statement as the Row Source property of the first combo box:
‘*********************
SELECT Left([IPAddress],InStrRev([IPAddress],’.’,-1,1)-1) AS IPAddSet FROM IPAddress GROUP BY Left([IPAddress],InStrRev([IPAddress],’.’,-1,1)-1) HAVING Taken=False ORDER BY Left([IPAddress],InStrRev([IPAddress],’.’,-1,1)-1)
‘*********************

3. But in this case you need to refresh the first combo box either when an existing IP Address selected or when a new one added by the user. I am not sure if AfterChange event occurs when a NotInList event occurs, so you might have to include the following code into both event handler.
‘*********************
Combo2.Requery
‘*********************

Play with it.

Good luck,

Arpi


-----Original Message-----

Hi Arpi

Regarding the last email i sent to you, i've sorted the problem out so the combo box brings up the IP address, in the code where do i put so that the second combo box only display address that are not taken, i set the table so it has the yes/no data type, but i'm not sure where to put it in the row source, can you help.

Darren

Darren,

I guess you need a report that lists all IP Addresses belonging to a previously selected set, and indicates if a particular IP Address is taken or not.

If this is the definition then:

You need to prompt the user to select the IP Address set she/he wants to have the report of.
This can be:
- a small form, where you can put a combo box that lists the sets of IP Addresses,
- or you can use the InputBox function that is used to prompt the user for input and returns the value the user typed. You cannot use combo box on an InputBox, it just has a text field.

Of course, a form with a combo box is more convenient for the user.

When you click OK on either boxes, you can pass the selected or typed item to the opening report in the WhereCondition parameter of the DoCmd.OpenForm statement.

The syntax of WhereCondition is just like the one of the WHERE clause of an SQL statement, but without the WHERE keyword.

For more info look up OpenForm in Help.

BRegs,

TBÁrpi

-----Original Message-----

Hey, thank you Arpi, cheers for your help, I couldn't get this far without your help, I got one more thing I need you help me with, I know how to create a report, but I need tohave a report the only displays the IP Address the user choose i.e. all taken or not taken for 126.10.12 and so on.
Just wondering would i need to create a new form so that user can choose the IP address and if its taken or not, which then create the report.

regards
Darren

Darren,

Check the code behind the form (btnViewReport_Click).

Generally, it checks the values of the controls on the form and composes the WhereCondition argument of the OpenForm method.

I hope this helps.

BRegs,

TBÁrpi

-----Original Message-----
Arpi

Sorry to bother youbut i don't seem to understand what you wrote on the forum, i try to do what you wrote, but it doesn't seem to what i want it to do, or maybe i making ths report harder then it should.

Attached is an example of the form design i want the search to look like;

The user would need to select the IP Address i.e. 126.10.101 and the report will print out all the IP Address that contains those numbers, and i know that will be contain in a combo box.
The other option the user need to choose is to pick if they want see IP Address that is free or taken, i put that in tick boxes.
But i can't seem to put the two together and print a right report can you help.

The database I'm creating allow user to add information in combo box which in turn update the table, i was wondering can it be done the opposite way deleting a field from the table and update the combo box.

You probably mean deleting a RECORD from a table and not a FIELD...

However:
- if you delete a FIELD that is a part of a combo's row source then you need to modify the combo's RowSource property in order to show the values you want otherwise it will cause run-time error;
- if you delete a RECORD from a table then you need just requery the combo by using its Requery method. As I know, a combo is automatically requeried when you step to an other record, but check this...

Sorry i want the user to be able to select a data from the combo box and be able to remove it by clicking on a command button, the data would be removed from the combo box and from the table.
How would i go about doing that

Use an SQL statement to delete the record:

"delete * from [table_name] where [field_name]='" & [combo_name] & "'"

where [table_name] is the name of your table, [field_name] is the name of the field, and [combo_name] is the name of your combo box.

then refresh the combo:

Me.[combo_name].Requery

where [combo_name] is the name of your combo box.