add entry to list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I hope some one can help with this.

I have a form with two listboxes it is being used as a way of allowing users
to add to the combo boxes in the database.

the first list box has a list of tables the bound column is a description of
the tables column 2 has the table name. clicking on a description on listbox
1 will show all the items in that table in listbox 2 these tables only have 1
field therefore only one column,

I have used (in After update):

On Error Resume Next
Select Case cboCountry.Value
Case "Consultants"
cboCity.RowSource = "tblConsultant"
Case "Genetic Mutations"
cboCity.RowSource = "tblMutations"
End Select

to populate listbox 2 this works perfectly

I would like to be able to add to listbox 2, by typing in a textbox and
pressing a button, this is where I am stuck, I am sure I can get the
tablename from listbox 1, column 2, but I have no idea how to code this, I am
having a severe brain block today. any ideas

thank you in advance

Phil
 
Not sure if that what you are asking for, but if you want to add a value to
the table that list box 2 currently display, try something like

*******************************
Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet, TableName As String

Set MyDb = CurrentDb

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select
Set MyRec = MyDb.OpenRecordSet ("Select * From " & TableName & " Where
FieldName = '" & Me.[TextBoxName] & "'" )
If Not MyRec.Eof Then
Msgbox "RecordExist"
Else
MyRec.AddNew
MyRec!FieldName = Me.[TextBoxName]
MyRec.Update
Me.cboCity.Requery
End If

**************************
If I'm not in the right direction please post back,
Note: don't forget to change the field name and text box name.
 
Thank you so much, that worked perfectly, this might be asking too much how
would I go about deleting the selected record.

if i am asking too much, I can repost. but thank you that just works

Thanks

Phil

Ofer Cohen said:
Not sure if that what you are asking for, but if you want to add a value to
the table that list box 2 currently display, try something like

*******************************
Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet, TableName As String

Set MyDb = CurrentDb

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select
Set MyRec = MyDb.OpenRecordSet ("Select * From " & TableName & " Where
FieldName = '" & Me.[TextBoxName] & "'" )
If Not MyRec.Eof Then
Msgbox "This Option Already Exists"
Else
MyRec.AddNew
MyRec!FieldName = Me.[TextBoxName]
MyRec.Update
Me.cboCity.Requery
End If

**************************
If I'm not in the right direction please post back,
Note: don't forget to change the field name and text box name.
--
Good Luck
BS"D


Phil said:
Hi, I hope some one can help with this.

I have a form with two listboxes it is being used as a way of allowing users
to add to the combo boxes in the database.

the first list box has a list of tables the bound column is a description of
the tables column 2 has the table name. clicking on a description on listbox
1 will show all the items in that table in listbox 2 these tables only have 1
field therefore only one column,

I have used (in After update):

On Error Resume Next
Select Case cboCountry.Value
Case "Consultants"
cboCity.RowSource = "tblConsultant"
Case "Genetic Mutations"
cboCity.RowSource = "tblMutations"
End Select

to populate listbox 2 this works perfectly

I would like to be able to add to listbox 2, by typing in a textbox and
pressing a button, this is where I am stuck, I am sure I can get the
tablename from listbox 1, column 2, but I have no idea how to code this, I am
having a severe brain block today. any ideas

thank you in advance

Phil
Click to expand...
Click to expand...
 
You can try something like

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select

CurrentDb.Execute "DELETE * FROM " & TableName & " Where FieldName = '" &
Me.cboCity & "'" , dbFailOnError
Me.cboCity.Requery

****Note ***
If the field you delete by is numeric. then remove the single quote

CurrentDb.Execute "DELETE * FROM " & TableName & " Where FieldName = " &
Me.cboCity , dbFailOnError

--
Good Luck
BS"D


Phil said:
Thank you so much, that worked perfectly, this might be asking too much how
would I go about deleting the selected record.

if i am asking too much, I can repost. but thank you that just works

Thanks

Phil

Ofer Cohen said:
Not sure if that what you are asking for, but if you want to add a value to
the table that list box 2 currently display, try something like

*******************************
Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet, TableName As String

Set MyDb = CurrentDb

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select
Set MyRec = MyDb.OpenRecordSet ("Select * From " & TableName & " Where
FieldName = '" & Me.[TextBoxName] & "'" )
If Not MyRec.Eof Then
Msgbox "This Option Already Exists"
Else
MyRec.AddNew
MyRec!FieldName = Me.[TextBoxName]
MyRec.Update
Me.cboCity.Requery
End If

**************************
If I'm not in the right direction please post back,
Note: don't forget to change the field name and text box name.
--
Good Luck
BS"D


Phil said:
Hi, I hope some one can help with this.

I have a form with two listboxes it is being used as a way of allowing users
to add to the combo boxes in the database.

the first list box has a list of tables the bound column is a description of
the tables column 2 has the table name. clicking on a description on listbox
1 will show all the items in that table in listbox 2 these tables only have 1
field therefore only one column,

I have used (in After update):

On Error Resume Next
Select Case cboCountry.Value
Case "Consultants"
cboCity.RowSource = "tblConsultant"
Case "Genetic Mutations"
cboCity.RowSource = "tblMutations"
End Select

to populate listbox 2 this works perfectly

I would like to be able to add to listbox 2, by typing in a textbox and
pressing a button, this is where I am stuck, I am sure I can get the
tablename from listbox 1, column 2, but I have no idea how to code this, I am
having a severe brain block today. any ideas

thank you in advance

Phil
Click to expand...
Click to expand...
Click to expand...
 
Thats perfect again, thanks a lot for this

Phil

Ofer Cohen said:
You can try something like

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select

CurrentDb.Execute "DELETE * FROM " & TableName & " Where FieldName = '" &
Me.cboCity & "'" , dbFailOnError
Me.cboCity.Requery

****Note ***
If the field you delete by is numeric. then remove the single quote

CurrentDb.Execute "DELETE * FROM " & TableName & " Where FieldName = " &
Me.cboCity , dbFailOnError

--
Good Luck
BS"D


Phil said:
Thank you so much, that worked perfectly, this might be asking too much how
would I go about deleting the selected record.

if i am asking too much, I can repost. but thank you that just works

Thanks

Phil

Ofer Cohen said:
Not sure if that what you are asking for, but if you want to add a value to
the table that list box 2 currently display, try something like

*******************************
Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet, TableName As String

Set MyDb = CurrentDb

Select Case cboCountry.Value
Case "Consultants"
TableName = "tblConsultant"
Case "Genetic Mutations"
TableName = "tblMutations"
End Select
Set MyRec = MyDb.OpenRecordSet ("Select * From " & TableName & " Where
FieldName = '" & Me.[TextBoxName] & "'" )
If Not MyRec.Eof Then
Msgbox "This Option Already Exists"
Else
MyRec.AddNew
MyRec!FieldName = Me.[TextBoxName]
MyRec.Update
Me.cboCity.Requery
End If

**************************
If I'm not in the right direction please post back,
Note: don't forget to change the field name and text box name.
--
Good Luck
BS"D


:

Hi, I hope some one can help with this.

I have a form with two listboxes it is being used as a way of allowing users
to add to the combo boxes in the database.

the first list box has a list of tables the bound column is a description of
the tables column 2 has the table name. clicking on a description on listbox
1 will show all the items in that table in listbox 2 these tables only have 1
field therefore only one column,

I have used (in After update):

On Error Resume Next
Select Case cboCountry.Value
Case "Consultants"
cboCity.RowSource = "tblConsultant"
Case "Genetic Mutations"
cboCity.RowSource = "tblMutations"
End Select

to populate listbox 2 this works perfectly

I would like to be able to add to listbox 2, by typing in a textbox and
pressing a button, this is where I am stuck, I am sure I can get the
tablename from listbox 1, column 2, but I have no idea how to code this, I am
having a severe brain block today. any ideas

thank you in advance

Phil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top