Linking a drop down list to a form

[papadega3000]

I have general question about queries and data entry form working together.

I have made a drop down list that queries a field spitting back all of the entered data in that field. The next action I want to do is upon selecting a certain item from that list I want to enter in the selected item and the newly entered data into a table.

Is this possible with an append query? Or would I have to generate some code to do this?

Any advice would help me understand what needs to be done.

Thanks

 

[Guus2005]

You could enter the form.fieldname in your appendquery.

insert into table (a, b) values ([form].[fieldname],[form].[fieldname2])

If that doesn't work you probably need to use VBA
and build your appendquery

 

[papadega3000]

Thanks for the reply. I actually starting doing that and it didn't work for me. But I have tried it in VBA and I used the INSERT statment to do so and I also added a section that won't allow data entry until the list item is selected. Now I am just debugging the event side of it. I am not sure where I should place it.

I have a click event procedure made that disables the input if there is nothing selected then once it is selected the input gets enabled. Then inserts the data into the table.

So my procedures should be like this:

1. Open form
2. Disable Data Entry Until Item is selected
3. Enable Data Entry
4. Type data in box
5. Insert both list item selected and text box to table

I can't have all of it in the same procedure because the data can't be entered until a value is selected or clicked. Otherwise they would be NULL.

I am assuming the procedures I will need is a OnClick then AfterInsert event which is a guess?. The onClick would disable everything until the value is selected then wait until the data is entered then insert it into the table.

If anyone can give me an idea if I am using the correct procedures.

Thanks in advance.

 

[Guus2005]

Not sure what the problem is.
You could use an unbound form and use VBA to check all necessary input and then create your insert query.
Perhaps you can post an example of the problem?

 

[papadega3000]

I wish I could it would be helpful but the data I am working is sensitive and cannot release it. I am just experiencing issues with the design because the db was given to me and I was told to make it work etc.,etc..

Anyway, what I have done so far if made an OnClick Event procedure.
This procedure is in an unbound form with a list box and two text boxes.

The list box queries another table X to populate the list of items. The two text boxes(Textbox A and textBox B are just data entry into the another table Y. Then what I want to do is have some VBA that will take the selection from list box and insert that selection and along with the entry for textBox A into Table Z.

What I have working so far is this.
I have an event procedure made for the listbox. This event procedure is an OnClick() which I was able to disable and re-enable data entry to Textbox A by using Me.Fieldname.Enable/Locked= True/False which is inside an IF Conditional that waits until you select something from the list. The problem is the Insert Statement is not inserting the data.

Here is some brief suedo code:

Private Sub test_Click()

IF (Listbox is not empty)

Me.Fieldname.Locked= True

Else
Me. Fieldname.Locked = False
Me. Fieldnmae.Enabled= True

Insert TableZ( Field1, Field2) Values( Value1, Value2)

End IF
End Sub

Thank you for your responses they have been helpful. Between this forum and google I am managing through these problems.

Let me know if you have any insight..and I'll keep trying to make what I have work.

Suggestions welcome.

 

[Guus2005]

Hope this is helpfull:

Private Sub Knop2_Click()

    Dim strSql  As String
    Dim intID   As Integer
    Dim strName As String
    
    intID = Me.lstMonth.Column(0)
    strName = Me.lstMonth.Column(1)
    strSql = "Insert into Tabel1 ( id, name ) Values (" & intID & ", '" & strName & "')"
    
    CurrentDb.Execute strSql
    
End Sub

It is difficult to pinpoint your exact problem. Based on the information you give, you seem to know what you're doing. (no pun intended)

 

[papadega3000]

Hello,

Thanks for your input... I will try and test it out tonight on my test db here at home. But when I get back to the actual project I will test it there.

It seems that it should work. I must be just missing something stupid.

Thanks again... and I will update this post if it works.

 

[papadega3000]

Hello,

I got around to testing your suggestion and the original way I had it setup it was giving me an Invalid use of Null error because I was using the the OnClick event and after I click a value from the drop down list it would error out because the selected value would get set to the variable name I declared but at that time the textbox field was not filled in yet so it reported a null value.

My solution was to eliminate the disabling and reenabling section for the time being and allow the entry of data but in order to invoke the sql statement you needed to click a check box. This eliminating the Null error but the problem is the SQL statement is not adding the record to the table that houses the data from the list box and text box of the form.

My code looks like this:

Private Sub Check11_Click()
Dim strSql As String
Dim str1 As String
Dim str2 As String

If Me.Check11 = False Then
MsgBox " Check this box in order to submit "
ElseIf Me.Listboxname = "" Or IsNull(Listboxname) Then
MsgBox " Please select a item from list"
ElseIf Me.Textboxname = "" Or IsNull(Me.textboxname) Then
MsgBox " Please type in the box"
Else
str1 = Me.Listboxname
str2 = Me.textboxname
strSql = " INSERT INTO Table X(Listboxfield,Textboxfield) VALUES ('" & str1 & "','" & str2 & "')"
CurrentDb.Execute strSql
End If

End Sub

This is not entering the data into table X.
Is there something I am doing wrong here????

 

[Guus2005]

Lose the "Table":

strSql = " INSERT INTO X(Listboxfield,Textboxfield) VALUES ('" & str1 & "','" & str2 & "')"

Enjoy!

 

[papadega3000]

I did ... still no go...I am actually going to go over my conditionals above that because I think they may/may not be preventing me from ever getting to the insert statement.

will update.

 

[Guus2005]

Can you:
-Export the form to an empty database.
-Create Empty Table X
-Compact and Zip it
-Post the result.
This should give me enough info to solve this strange problem...

 

[papadega3000]

I am sorry... I cannot distribute my work because it contains sensitive data.

It would make it easier to solve but cannot be done. I appreciate all of your help.

I am going to be debugging that section of code shortly I am currently working on another task which is almost complete.