By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Creating a text box search to autofill all fields on a form

P: 3
The solution is probably simple enough but i am new to acces and have very limited experience with VBA.

We currently have a database that consists of one considerably large table (tbl_SBT) with upwards of about 30 columns.
I have created a form (frm_SBT) for typing in sample information to be added to the database.

Sometimes samples that have already been entered have identical information in many of the 30 or so fields. Or they need to be updated with new information.

What i would like to have, is a search box in the header where you can enter a sample number which is also the primary key (long integer). This will trigger the information in the corresponding columns to be autofilled on the form.
This can basically be done by selecting the primary key field on the form and clicking "search" in the ribbon and entering the sample number.

But we would like it to be more intuitive with a search text box with a button in the header to carry out the same process. Can this be done?

Thanks in advance!

Mark
Jan 30 '17 #1

✓ answered by Seth Schrock

You would just need to add that field to your SELECT statement. There are many ways to do this. You could put another textbox on your form for the user to enter the new PK value or you could use an Input Box. That part is up to you. But this is how you will need to change your code.
Expand|Select|Wrap|Line Numbers
  1. Dim lngPK As Long
  2. Dim strSQL As String
  3.  
  4. lngPK = Me.txtPK 'This is where you need to decide how to get the value
  5.  
  6. strSQL = "INSERT INTO Your_Table " & _
  7.          "SELECT " & lngPK & " As PK_FieldName, Field1, Field2, ... " & _
  8.          "FROM Your_Table " & _
  9.          "WHERE PK_Field = PK_Value"
  10.  
Note the As PK_FieldName needs changed to the correct field name.

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
Let me make sure that I understand what you are trying to do. You want to search for a record based on its primary key value and then create a new record that is an exact copy of the existing record. Is this correct? How will the user know the primary key value to search for? Are you wanting to search for the record in the textbox or select the record in the textbox?
Jan 30 '17 #2

P: 3
That's exactly what i want to do. It's a weird system but it works for the ones who use it. And they know which primary key value to search for because the ones that use the database have it all on paper.

I guess i want to select the record using the search box.

Example: We get samples in everyday. Some samples need to be analyzed 3 different ways, others need to be analyzed 8 different ways. Each analysis needs a new record but they all contain the same sample information.
So instead of typing in the same information 8 times they can just type in the primary key value and add the same record again.
They also need to be able to search for a primary key value so they can update the record in the database, when the results are in after a few days.
Jan 30 '17 #3

Seth Schrock
Expert 2.5K+
P: 2,931
Then all you need to do is run an INSERT query pulling the values from the existing record.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Your_Table
  2. SELECT Field1, Field2, ...
  3. FROM Your_Table
  4. WHERE PK_Field = PK_Value
Make sure to not include the PK field in the SELECT statement, but include all other fields that you want copied. Use the PK value in your textbox as the PK_Value in the WHERE clause and then execute the query.
Jan 30 '17 #4

P: 3
Thank you. This works nicely.

I need one last line of code i think. Can i specify which primary key value to save the record in? So sample 7974 needs to saved again as 7975 or 7976. Now it saves it with a primary key value as 0.
Jan 31 '17 #5

Seth Schrock
Expert 2.5K+
P: 2,931
You would just need to add that field to your SELECT statement. There are many ways to do this. You could put another textbox on your form for the user to enter the new PK value or you could use an Input Box. That part is up to you. But this is how you will need to change your code.
Expand|Select|Wrap|Line Numbers
  1. Dim lngPK As Long
  2. Dim strSQL As String
  3.  
  4. lngPK = Me.txtPK 'This is where you need to decide how to get the value
  5.  
  6. strSQL = "INSERT INTO Your_Table " & _
  7.          "SELECT " & lngPK & " As PK_FieldName, Field1, Field2, ... " & _
  8.          "FROM Your_Table " & _
  9.          "WHERE PK_Field = PK_Value"
  10.  
Note the As PK_FieldName needs changed to the correct field name.
Jan 31 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.