Putting form data into a temporary table

[randommetalguy]

Hey,

I have an input form with about 8 text boxes on it. I want to store the values from the 8 text boxes in a table so I can access the values later. I have a table called tblInputForm that I want to store the data in.

When I click a submit button how do I store the text box values into the temporary table?

Would it be something like

Insert InputForm.textbox1.value into tblInputForm Field1

Also when would be the best time to delete the data in tblInputForm? I was thinking whenever the form is loaded to clear the textbox values and make sure the tblInputForm is empty.

 

[pbaldy]

The simplest way would be to bind the form to the table and the textboxes to their respective fields. In the load event of the form:

CurrentDb.Execute "DELETE * FROM tblInputForm"

 

[randommetalguy]

Okay and how would I query based on what's the in the temporary table? Right now I have it set up like this is my main query

Like [Forms]![Form1].[LookUpField]));

Can I just change the like statement to [Tables]![tblInputForm].[LookUpField]

 

[pbaldy]

Presuming you make sure the table only has one record, you can join the table in queries without an actual join:

SELECT...
FROM MainTable, tblInputForm
WHERE Maintable.FieldName = tblInputForm.LookUpField

 

[randommetalguy]

Okay that makes sense and yes the tblInputForm will only have one row.

This is kind of different but relates to the last SQL statement you typed.

The results of the query below we'll name it Query1:

SELECT...
FROM MainTable, tblInputForm
WHERE Maintable.FieldName = tblInputForm.LookUpField

I will need to use one of the fields from this query as the basis of another query. I have the query correct but in Access it keeps prompting me to enter in the value instead of looking it up automatically.

The SQLstatement of my second query looks like this:

SELECT ....
FROM AnotherTable
WHERE AnotherTable.FieldName like [Queries]![Query1].[FieldName]

When I have that statement and the query runs I have to type in the value O6.8 instead of [Queries]![Query1].[FieldName] which is O6.8 getting put in automatically.

 

[pbaldy]

You can't reference table or query fields that way. You either need to build one query on top of another (using the first query like a table for the second), join them together, use one as a subquery, use DLookup, etc. The best solution depends on exactly what you're trying to do.

 

[randommetalguy]

Okay,

Input: employeeID

Query 1:

SELECT FirstName, LastName, JobNumber
FROM tblEmployee, tblInputForm
WHERE tblEmployee.employeeID = tblInputForm.Input

so if Query1 Returns

FirstName: John
LastName: Smith
JobNumber: 012345

This is another table:

JobTable
JobNumber: 012345
JobName: Purchasing Agent
JobSalary: $45,000

I want query two to automatically take 012345 and give me the salary associated with number 012345

How can I create two queries or what would the SQL look like that joins them? To return $45,000 by inputting the employeeID?

 

[pbaldy]

I'm not great with SQL off the top of my head. Try starting a new query, and add all three tables to the grid. If one doesn't already exist, drag a join line between the JobNumber fields. Add the fields you want returned from each table, and keep that criteria. See if that gets you what you want. If not, can you post a sample db? I'm juggling projects at work, and my brain is cramping up.

 

[randommetalguy]

Yea right now I'm trying to tie my input text boxes to the tblInput. I'll see what happens and I'll get back to you.

 

[randommetalguy]

Alright, I'm trying to tie my text boxes to the table. I have

Textbox1 and under it's properties I put the following in the Control Source property: =[tblInputForm]![Field1] and I get #Name? in all of the text boxes I did this for.

I thought this is what "bind the form to the table" means. Am I wrong?

 

[pbaldy]

No, you set the Record Source of the form to the table (that binds the form to the table), then the Control Source of the textbox would simply be:

Field1

 

[gemma-the-husky]

if the data doesnt need to be kept from session to session, its easier to store it in variables, surely

 

[randommetalguy]

Well I would be using variables, but in the future this will be a web application and I will want to use tables as much as possible.

I have my form linked to my tblInputForm right now sometimes I get #DELETED in my text boxes. What do I do to get rid of this problem?

 

[randommetalguy]

http://userpages.umbc.edu/~cibor1/MyDatabase.mdb

That is how you can access my database.

The two main problems I am having right now are when I load the form I get "#DELETED" in all of my text boxes. I've tried Inserting a dummy row after the delete command and inputting a dummy Primary Key, but my tblInputForm is not getting update with the data from the form.

The other problem I am having is getting the queries to automate themselves. The 'ASBasicQ' works properly but the other two queries are turning into a parameter queries. I've tried nesting SQL statements to avoid this problem but I still have to input parameters in order to get the queries to run.

Thanks for all your help again guys.

 

[pbaldy]

The first problem is a "my bad", as you wouldn't want to delete the single record on load. One option would be to simply leave the choices there, if the user might want to use them over. If not, just set each of the values to Null.

For the queries, why not just join the table in as you did with the first one?

 

[randommetalguy]

Alright I've got my queries automated now and they are returning the proper values.

But, my input form still isn't working. I only want my input form to have 1 record at any given time. So on load I have this piece of code:

Private Sub Form_Load()
CurrentDb.Execute "Delete * FROM tblInputForm"
CurrentDb.Execute "Insert INTO tblInputForm " & "(InputBeltWidth,InputWorkingTension,InputSafetyFactor,InputTopCoverThickness,InputBottomCoverThickness,InputTopCoverCode,InputBottomCoverCode,InputCoreRubberCode,InputSTNO) VALUES " & "('', '', '6.7', '', '', '', '', '', 'ST-NO');"
End Sub

Then when you click build-a-belt I want to delete that default null value statements and insert the values that are on the form. So here's what my click build-a-belt code looks like:

Private Sub cmdBuild_Click()
CurrentDb.Execute "Delete * FROM tblInputForm"
CurrentDb.Execute "Insert INTO tblInputForm " & "(InputBeltWidth,InputWorkingTension,InputSafetyFactor,InputTopCoverThickness,InputBottomCoverThickness,InputTopCoverCode,InputBottomCoverCode,InputCoreRubberCode,InputSTNO) VALUES " & "('1200', '370', '6.7', '22.0', '8.0', '', '', '', 'ST-2240');"

run queries
show report
EndSub

Now right now I've basically hard coded what I want to work into the VB code. I've tried putting me.txtBeltWidth.value in the 1200 (where I've bolded) but I get errors back.

What's the best way to get the values from the form into that table without using variables?

 

[randommetalguy]

Finally got it working!!!

I had to do the " & variableName & ' trick but now it's working.

I did have to use variables, but when this becomes a web project the VB code should convert nicely into PHP and my SQL statements will already be generated from Access.

Thanks everyone especially you pbaldy!

 

[randommetalguy]

Alright, this problem seems to have manifested itself again.

Once again, I have an input form with about 8 text boxes. I want the values of all of those text boxes stored into a temporary table.

Right now here is my code for when the form loads:

CurrentDb.Execute "Delete * FROM tblInputForm"
CurrentDb.Execute "Insert INTO tblInputForm " & "(InputBeltWidth,InputWorkingTension,InputSafetyFactor,InputTopCoverThickness,InputBottomCoverThickness,InputTopCoverCode,InputBottomCoverCode,InputCoreRubberCode,InputSTNO) VALUES " & "('', '', '6.7', '', '', '', '', '', 'ST-NO');"

However, when the form loads I get #Deleted in all of my text boxes. I am unable to type into the text boxes because it says the record has been deleted.

I really don't want to store any data into Visual Basic variables, I would rather store them in a table for manipulation later.

One thing I have noticed is that if I hit F5 the INSERT statement seems to run and I can run my application just fine. So maybe what it boils down to (assuming my code is correct) what's the Visual Basic code to refresh a form automatically?

Me.Show?

 

[gemma-the-husky]

possibly
a) the form binds to the current table
b) then you delete the item, so the current record is deleted
c) then you add a new record, but this isnt part of the bound recordset (yet)

so perhaps the easiest way is to set the forms recordsource AFTER the two execute statements

CurrentDb.Execute "Delete * FROM tblInputForm"
CurrentDb.Execute "Insert INTO tblInputForm " & "(InputBeltWidth,InputWorkingTension,InputSafetyFa ctor,InputTopCoverThickness,InputBottomCoverThickn ess,InputTopCoverCode,InputBottomCoverCode,InputCo reRubberCode,InputSTNO) VALUES " & "('', '', '6.7', '', '', '', '', '', 'ST-NO');"