Need help constructing a form 1

[shteev]

I'm building a form in MS Access which allows the user to select a record each from 2 different tables (using combo boxes and/or drop boxes), display data from all fields of both records selected, and then add a record to a third table, constructed from data from the 2 records selected (the third table is a cross-refernced table which links the first 2 tables).

What I need to know is:

a) How do I dymanically alter the contents of text boxes based on input from a combo box elsewhere on the form? It's easy enough if the form is bound to a single table, but this form isn't.

b) How do I add a record into the third table, based on data held within text boxes on the form?

 

[mndrlion]

quote]a) How do I dymanically alter the contents of text boxes based on input from a combo box elsewhere on the form?
[/quote]

In the Change event or the After Update event of the combo box create an event procedure to set the value of the text box. Like this:

Private Sub cboBox_AfterUpdate()
    txtBox = "some expression"
End Sub

b) How do I add a record into the third table, based on data held within text boxes on the form?

You will need to create an append query in your code. Like this:

Dim strSQL as String

strSQL = "INSERT INTO tblName ( TextField1, NumberField2 ) " & _
         "Values(""" & strVariable & """," & numVariable & ");"

currentdb.execute strSQL

 

[dhookom]

a) what text boxes? Could you provide more information?
b)If your form isn't bound, you could place a command button on the form that would run an insert query:

Dim strSQL as String
strSQL = "INSERT INTO tblA (FieldText, FieldNumber) " & _
   "Values(""" & Me.cboFieldText & """, " & Me.cboFieldNumber & ")"
DoCmd.RunSQL strSQL

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[shteev]

I was hoping to get away without using Visual Basic cos I'm not very skilled with it. But it doesn't look too hard to achieve.

I don't fully understand the code you've written, tho; It's constructing an INSERT query on the fly, but whats with the _ and """ characters?

 

[mndrlion]

whats with the _ and """ characters?

The underscore at the end of a line tells VB that the statement is continued on the next line.

To include a double quote as part of your string, you need 2 of them.

This

MyString = "He said, ""What's up!"""

sets MyString to
[blue] He said, "What's up!"[/blue]

 

[shteev]

Ok, I think I understand, but there's a problem and I'm clueless how to solve it.

I've simplified the code down to this:

Private Sub Attach_Comment_Button_Click()

Dim strSQL As String

strSQL = "INSERT INTO CommentXREFQuestionnaire" & _
    "VALUES(""28"", ""E"", ""2"");"

DoCmd.RunSQL strSQL
    
End Sub

and it gives me 'Runtime error 3134: Syntax Error in INSERT INTO statement'

But I've constructed this as an append query in the 'Queries' section of the database and it works fine:

INSERT INTO CommentXREFQuestionnaire
VALUES ("28", "E", "2");

It's not a spelling mistake, or the fact that the record has been appended once and cannot be appended again due to key violations; so whats the problem?

 

[shteev]

My other problem is how to GET data from elements in the form, so I can pass that data to other elements.

Specifically: I have 2 subforms on my form, both showing entire tables, as a datasheet view (so the user can sort/filter the data shown). I wish to select one record from each of these subforms; then display single or multiple fields from those records elsewhere on the form.

e.g. I select record 7 from a subform showing tableA, so I instantly want the data from field 1 of record 7 to be displayed in textbox1; or, allow the SQL string I'm contructing above to access data from this field.

 

[mndrlion]

gives me 'Runtime error 3134: Syntax Error in INSERT INTO statement'

There needs to be a space between CommentXREFQuestionnaire and the VALUES keyword. Add a space after CommentXREFQuestionnaire and before the closing quote.

strSQL = "INSERT INTO CommentXREFQuestionnaire " & _
"VALUES(""28"", ""E"", ""2"");"

 

[shteev]

Ahh, lovely, I knew it couldn't be anything too complicated.

Can anyone help with my second problem?

 

[mndrlion]

I select record 7 from a subform showing tableA, so I instantly want the data from field 1 of record 7 to be displayed in textbox1

In the Current event on the subform you can do something like this
textbox1 = field1

If textbox1 is on the main form then try this
Parent![textbox1] = field1