NEED HELP! Adding row to table using VB

[Evenjelith]

Hi, my boss wants me to fix up the company database; adding a button which puts a new table entry provided by the user into the database. I have all the necessary forms I require but the problem is my VB code. I've pillaged some code from other threads on this site and tried to rework it for the job i've been given but I keep getting exceptions. First i was getting that a user variable was undefined or something like that and now im getting expected end of statement.

The compiler HATES the Handles addWellButton.click at the end and i have no idea what it's needed for but it's been in quite a few examples ive looked at for this kind of problem so if someone could tell me what it is that would be great.

The user enters a well name in a text box and then hits the add button. The text is then passed into a new row in the table and all other columns are given a null value.

Any help at all would be hugely appreciated!

Here's the code I'm using:

Private Sub addWellButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addWellButton.Click

Dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:\WELLS\Database\Database.mdb;User Id=;Password=;"
Dim tConnection As New OleDb.OleDbConnection(Conn)
Dim SQL As String = "INSERT INTO Wells Database ([Well Name-#], Type, Result, Status, Surveyed, Drilling Contractor, Precollar Rig, Rig, Shift Duration, Spud Date, TD Date, Plugged Back TD, Rig Release Date, Lot and Plan, Comments, Days Offsite) VALUES(@[Well Name-#], null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)"
Dim tCommand As New OleDb.OleDbCommand(SQL, tConnection)
WellName = txtAddWellName.Value

If (IsNull(WellName)) Then
MsgBox ("Please enter a well name"), vbOKOnly, "Re-enter"
Else
With tCommand
.Parameters.Add("@[Well Name-#]", OleDb.OleDbType.VarChar)
.Parameters(0).Value = txtAddWellName.Text
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
End With
MsgBox (WellName + " added to database"), vbOKOnly, "Well Added"

End If
End Sub

 

[TylerTand]

If I get this right all you want to do is add the Name of a new Well that is inputted through a text box into a Table?

From you code it looks like the Table to be added to is called: Wells
I don't see what the text box on the form is Named so I am going to call it: [text5]

Set Varset= currentdb.openrecordset("Select Wells.* FROM Wells;",2)
Varset.AddNew
Varset!WellName= me![text5]
Varset.Update

Create the button and place all the code in on click event

The well Name will be filled from the Text box and all the other fields will be left blank or Null.

Hope this helps,
Tyler

 

[Evenjelith]

Great Help. One more question please.

Hey thanks for that, it works great.

There is a problem though. I have a primary form called Company Wells Database. You click the add well button on this form to take you to a second form entitled addWellName. This second form is where i perform the add method which is working fine. However, when i search for the new well on the initial form, although it finds the well and displays the entry, it does not show the well name in the appropriate label spot (the field is left blank). This can be fixed if i close this first form and reopen it (the field will then show the name). I tried refreshing the form itself but the problem persisted. I think what the problem is is that I need to refresh a query related to this form (so when i close and open the form again it recalls this query). What I've done is include a line of code to refresh the query in question but it doesnt seem to work. Do you have any suggetions?

I also want to add a check to make sure that the well name does not already exist in the table, because if it does i get an error message and a crash. However, I am having difficulty with writing the WHERE statment correctly.

Finally, I want the first form (the one that has the query that needs to be refreshed) to show the new entry into the table once it has been entered. So if a user selects NO when prompted if they wish to enter a new well, the code should close frmAddWell and display this table entry in the first form. My last line isnt liked by access though.

Tables:
Company Wells Database

Forms:
addWellName
Company Wells Database

Text Box:
txtAddWellName

Private Sub addWellButton_Click()

If (IsNull(txtAddWellName)) Then
MsgBox ("Please enter a well name."), vbOKOnly, "Please re-enter"
Else
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [Company Wells Database] WHERE ([Well Name-#] Like '*" & Me.txtAddWellName.Text & "*'))"

If rs.RecordCount = 1 Then
txtAddWellName.SetFocus
MsgBox ("'" + txtAddWellName.Text + "' already exists."), vbOKOnly, "Please re-enter"
Else
Set Varset = CurrentDb.OpenRecordset("Select [Company Wells Database].* FROM [Company Wells Database];", 2)
Varset.AddNew
Varset![Well Name-#] = Me![txtAddWellName]
Varset.Update
txtAddWellName.SetFocus
If MsgBox("'" + txtAddWellName.Text + "' added to database. Add another well?", vbYesNo, "Well Added") = vbYes Then
txtAddWellName.SetFocus
txtAddWellName.Value = Null
Else
DoCmd.OpenForm "Company Wells Database"
DoCmd.GoToControl "Well Name-#"
DoCmd.Close acForm, "frmAddWell"
DoCmd.FindRecord txtAddWellName
End If

End If

Set rs = Nothing
Set db = Nothing

Queries![Brad - Basic well data table].Refresh

End If

End Sub


Thanks so much for your help already. If you have any idea how to solve these last three hickups i would be very grateful.

 

[georgedwilkinson]

You mention a crash but you gave no details. Where does it crash? How do you know it crashed? Is there an error message? Does it point to a certain line of code or subroutine name? Does the application exit? Does Windows exit? Is there a mushroom cloud?

What does "cant write the WHERE statement correctly for the code to do what i want" mean? Why can't you? What does it do/not do? What do you "want"? What tables/columns are going to supply you with what you want?

Since it is a bit vague all I can offer is a few pointers totally unrelated to your problem (maybe).

You should never name database objects with spaces or special characters (i.e. "-", " ", "#")...it causes grief and heartache.

I'm also curious why you would add a well via code when you could do it right on the data entry form? Microsoft spent all that money to get their forms to work flawlessly with databases and I'm just wondering why you are not taking advantage of it.

 

[Evenjelith]

Thanks for your reply.

I have no control over the names of the database unfortunately but i use brackets [Company Wells Database] to avoid any issues with spaces.

To clarify (and also I’ve updated my above post with a new question), i have three problems:

1. I am not sure how to write the syntax of my WHERE statement. I want it to say where the value in the textbox (txtAddWellName) is the same as a value in the column ([Well Name-#]) of the [Company Wells Database] Table. The code i have used is modified from someone attempting a very similar problem in a different forum and not at all my own so I am unsure of what i need to do to rectify the WHERE to work syntactically. When i said Crash, i should have really said i get an error and so the process in a sence crashes. No crashing of Access occurs though sorry. My bad.

2. My second problem with regards to updating the first form [Company Wells Database] (and yes it is horrible to have a form and database of the same name but i have no choice in this either as I’m building on someone else’s work), although the new code I’ve been given will add the new entry to the table, when i go back to the original form (which remains open through the process) and search for the new well, i find it but when i open the new well in the table it does not display the well name (the only bit of information entered into the table so far). The other fields are blank also as they should be but the name field should have a value i.e. the one that i entered into the add well form. Since this problem disappears if i close and reopen this first form, i have to assume the problem lies with refreshing something. I tried code that refreshed the form:
Forms![Company Wells Database].Refresh
but this did not fix the problem so now i believe the issue is tied to a query run by the form to retrieve the values that go into these label spaces. I now want to write a piece of code to refresh the query in the hopes that will work but access gives me an error when it tries to execute the code so I’ve obviously done something wrong:
Queries![Brad - Basic well data table].Refresh

3. My last problem is to do with a YesNo message box. If someone enters a well and selects NO (they don’t want to add a new well) the form closes and they are looking once again at the original form. I want this original form to be showing the details of the new well that has been added. This is tied in a way to problem two as i want them to see the well name (which is currently not showing) and nothing else. However my code is not written correctly to do this and i was hoping that someone might have an idea of how to fix it. The problem lies with the last line where i tell it to search for an entry matching what’s in the textbox.


Once again thanks for a speedy reply and i hope that my problems are now a little clearer. With regards to functions integrated by Microsoft that i should be taking advantage of, if you had any interesting tutorials or articles to link me to on these topics i would be very appreciative but as it stands I’m not entirely sure what you mean.

 

[TylerTand]

#1
Set NameCheck= currentdb.openrecordset("Select [Company Wells Database].* from [Company Wells Database] WHERE [Company Wells Database].[Well Name- #]=me![txtAddWellName];",2)
if NameCheck.eof then
'Put your code to add the new Well Name here
else
msgbox " This well Name is already in the database"
end if
To explain this code so you can learn: You are opening up a recordset, which is very much like a query only in the code. The recordset says,"Give me everything in the Company Wells Database Table where the Well Name is the same as the text field named txtAddWellName on this form. The me! part means look on the current form. It is an easy syntax so that you don't have to spell out the whole thing. "if NameCheck.eof" means that if the recordset returns no values (EOF - End of File) then it means that the name doesn't exist in the table and you can use it. Else- otherwise pop up a message box telling me that the name already exists.
This will work if the text field, ([txtAddWellName]) is on the same form as the button executing the code. If it is not then you need to spell out the path to the proper form: ie .....WHERE [Company Wells Database].[Well Name- #]=[forms]![Form where field is]![txtAddWellName]
This will solve problem number 1

#2
Since the first form stays open, and it is filtered when you open it, it is not going to show the newly added Well Name until you requery the form. You can accomplish this in two ways. One way is that when you open up the addWellName Form that you close the Company Wells Database Form. This will ensure that when you open it up again it will include the changes. to do this put the following code in with the button that opens the addwellName form. You will already have:
docmd.openform "addwellName"
-just add this code after it
docmd.close acform, "[Company Wells Database]"
Then when you are done adding the New Well name and any other data about the well and you open the [Company Wells Database] Form all your new changes will appear. Put a Return to Main Menu Button or what ever Title you want and put :
docmd.openform "Company Wells Database"
docmd.close acform, "addwellName"
If you haven't made a button before just go to the tool bar and look for the Command button icon. Click it and drag a square on the form. Hit Cancel and look for the On Click line in the box. Hit the down arrow and select Event Procedure and then hit the (...) elipse. Tis will open the VBA window and you can paste in the open and close forms code above.
Remember to always open the new form first and then close the old one. This ensures that there is no lag between the forms and you see the database window or a blank page.

The Second way is just to pass on an open command with the new criteria
At the end of the adding procces put:
docmd.openform "Company Wells Database",,,"[Well Name- #]=me![Well Name- #]"
Now this is assuming that the Addwell Name form is set to only add one well at a time. Otherwise the code will get confused. This code will in a sense just requery the form with the new Well name's data.

#3
This part is a little confusing. Where does the YesNo message box come from? Is it the result of pushing a button? If so then all you have to do is add the same code above to the else portion of the messagebox.
I am assuming you have code similar to this for the messagebox:
Message box with Code based on Yes or No to Question:
Dim Answer As String
Dim Question As String
'Place your text here
Question = "Do you want to add a new Well?"
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo)
If Answer = vbNo Then
docmd.openform "Company Wells Database",,,"[Well Name- #]=me![Well Name- #]"
docmd.close acform, "addwellName"
Else
'Code for Yes button Press
End If

This will allow you to return to the Company Well Database form filtered to the New Well Name if the answer is No. You will have to supply what they are to do if the answer is yes.

I hope this helps you. I learned from the help of others. I have a suggestion for you. When you get stuck, use google. Just search for Access VBA and the function you are trying to do. It saves me all the time.
Tyler

 

[Evenjelith]

Hey thanks for getting back to me.

Your answer to question 1 looks like exactly what I want but I’m getting an error at the moment on the Set namecheck line:
Run-time error '3061': Too few parameters. Expected 1.

I want to use your second option for question two as I don’t want to close the main form (company wells database) if at all possible. However when you say "cannot add more then one well at a time" do you mean simultaneously or over the time that form is active. If the later is the case, could I simply reopen the form in the code for each new well the user wants to add to get around this?

Here is the current process for a user to add a well:
A user starts on the main form (Company wells database) clicks a button at the top for add well and is taken to the addwell form (company wells database is still active in the background). They enter a value into a textbox and press the add button on this new form. The code checks to see if it exists and if it doesn’t adds this new well and then brings up a yesno messagebox asking if they want to add more wells. If the user hits no the addwell form closes and the main form is displaying the new wells details (which will only be the well name and a lot of blank categories). If they hit yes then the addwell form doesn’t close but the textbox value is made null and the user enters a new name to be added and thus the cycle continues.

Also when I tried to implement your code (the second one) for updating the main form with the newly added wells details I get this issue (even if I just add one well before leaving the form). The main form prompts me to enter:
me![Well Name-#]
when the main form (company wells database) opens. So the statement where it is passed a value is not working. I'm not sure what is wrong however.

Here is my latest code:

Private Sub addWellButton_Click()

'Checks if any input was given
If (IsNull(txtAddWellName)) Then
MsgBox ("Please enter a well name."), vbOKOnly, "Please re-enter"
Else
'Search if well name exists
Set NameCheck = CurrentDb.OpenRecordset("Select [Company Wells Database].* from [Company Wells Database] WHERE [Company Wells Database].[Well Name-#]=me![txtAddWellName];", 2)

'If it doesnt exist
If NameCheck.EOF Then
'Add well code
Set Varset = CurrentDb.OpenRecordset("Select [Company Wells Database].* FROM [Company Wells Database];", 2)
Varset.AddNew
Varset![Well Name-#] = Me![txtAddWellName]
Varset.Update
txtAddWellName.SetFocus

'YesNo Messagebox asking if they want to add another well
If MsgBox("'" + txtAddWellName.Text + "' added to database. Add another well?", vbYesNo, "Well Added") = vbYes Then
'Sets textbox to null for next name entry
txtAddWellName.SetFocus
txtAddWellName.Value = Null
Else
'Closes addwell form and updates company form with last well added
DoCmd.OpenForm "Company Wells Database", , , "[Well Name-#]=me![Well Name-#]"
DoCmd.Close acForm, "frmAddWellDONTDELETE"
End If

'If it does exist
Else
txtAddWellName.SetFocus
MsgBox ("'" + txtAddWellName.Text + "' already exists."), vbOKOnly, "Please re-enter"
End If

End If

End Sub

Once again thanks for getting back to me and if you have time I’d really appreciate hearing back from you again. In any case, thanks heaps for everything you’ve helped me with so far. I really appreciate it.

 

[TylerTand]

I'm still learning to so here was my mistake number one:

Set NameCheck= currentdb.openrecordset("Select [Company Wells Database].* from [Company Wells Database] WHERE [Company Wells Database].[Well Name- #]=me![txtAddWellName];",2)

Should Read:

Set NameCheck= currentdb.openrecordset("Select [Company Wells Database].* from [Company Wells Database] WHERE [Company Wells Database].[Well Name- #]='" & me![txtAddWellName] & "';",2)

The Select statement is a string. Double quotes begin and end a string. In this string you want to drop out of the string and input a value. In your case the name of the well from the text field txtAddWellName. So in order to do this you have to close the string, get the value and reopen the string. If the field that you are referencing is a text field than you need a single quote and a double quote a space the & a space and in our case the me![textAddWellName]. The same syntax before and after. For the future, if the field is a number field you don't need the preceeding and ending single quotes. This will correct your error message about expecting one. The one being the WHERE clause didn't have a value.


quote:"I want to use your second option for question two as I don’t want to close the main form (company wells database) if at all possible. However when you say "cannot add more then one well at a time" do you mean simultaneously or over the time that form is active. If the later is the case, could I simply reopen the form in the code for each new well the user wants to add to get around this?"

Reply:

I am assuming that the Add Well Name form is set to Single and not continuous. If so, you can only add one well name at a time. If the data entry properties are set to yes, then you will always have a blank for that is ready for new data. This is the best way to create a new data entry form. You could fix your no duplicate well name problem by setting the Well Name field to not allow duplicates. To do this you will have to get to the Table and go to the design view and select the Well Name Field and on the general tab below find Index. Change the value from no to Yes (No Duplicates). This will prevent any two Well names from ever being the same. But any small difference and it will accept it. If you think the Well Names are unique enough than this could solve that problem. Then when ever someone tries to add a new name and it is the same you will get an error saying it is a duplicate. But using a recordset to check isn't really hard either.

quote: "could I simply reopen the form in the code for each new well the user wants to add to get around this?"

Reply: Yes. All you have to do is requery the form from a button and it will be blank again since the form will be set to data entry. If you don't know how to set the form to data entry let me know and I'll explain it.

Quote: "The code checks to see if it exists and if it doesn’t adds this new well and then brings up a yesno messagebox asking if they want to add more wells."

This Add button is where you would set the form to requery if the response was yes.
if VByes then [forms]![AddWellName].requery
This will blank out the form and allow the user to add another Well.

quote:" If they hit yes then the addwell form doesn’t close but the textbox value is made null and the user enters a new name to be added and thus the cycle continues."

Reply: If the Addwell form again is set to data entry you wouldn't need to set any field to null as the form would automatically be blank. Also when a form is set to data entry, as soon as data is entered it is immediately put in the table. You don't need a save button. If they added only one well, and you had a close button it could just close the form and also requery the Main form with that information displayed.

quote: "Also when I tried to implement your code (the second one) for updating the main form with the newly added wells details I get this issue (even if I just add one well before leaving the form). The main form prompts me to enter:
me![Well Name-#]
when the main form (company wells database) opens. So the statement where it is passed a value is not working. I'm not sure what is wrong however."

Reply: I think this issue will be resolved when you fix the first code. the inpropery me![Well Name-#] reference should fix that. Otherwise it is telling you that have a field named me![Well Name-#] and it need a value for it. Fix the first stuff and see if this is still a prolem and let me know.

Tyler

P.S. By the way, where I live this kind of help cost you a burrito!

 

[Evenjelith]

Tyler you are just too good to me.

Firstly, your fix for #1 works perfectly and I can now check if a duplicate well exists.

There are two little things though that I'm still having trouble over.

I'm having trouble getting the requery method to work though. As it stands nothing happens when the code executes. I tried your code:

vbYes Then
[Forms]![frmAddWell].Requery

and this:

vbYes Then
Me.Form.Requery

and this:

vbYes Then
Set addMoreR = [Forms]![frmAddWell]
addMoreR.Requery
addMoreR = Null

None of these approaches provoked any response from the form but there was no error either.
Can I use this requery method to solve the issue I have with the main form (Company Wells Database) needing to be reopened in order to show new wells added to the database?

Lastly, the main form still demands i specify Me!WellName when I select NO (add no more wells) on the addwell form. I've tried both your code:

DoCmd.OpenForm "Company Wells Database", , , "[Well Name-#]=me![Well Name-#]"
DoCmd.Close acForm, "frmAddWell"

And an alternative:

DoCmd.Close acForm, "Company Wells Database"
DoCmd.OpenForm "Company Wells Database"
DoCmd.GoToControl "Well Name-#"
DoCmd.FindRecord "Me![Well Name-#]"
DoCmd.Close acForm, "frmAddWell"

The alternative does not produce this request for input, However, I dont think I'm referencing the well properly because instead of showing the new well it just shows a blank template. I closed and reopened the main form in this test to ensure that the requery problem was not also the cause of this but there was no difference.

Thanks again man, you've been a huge help. The highlight of my day when I get into work.

 

[TylerTand]

Your Welcome!

I have not had to do the refresh method before because in my database when I am done with a form I close it. So you may have to try out a couple of things. First off in your three examples you included I hope that the code was preceded by an 'if' ? If not then this is your problem.
Example:

Dim Answer As String
Dim Question As String
'Place your text here
Question = "Do you Like Trucks?"
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo)
If Answer = vbNo Then
'Code for No button Press
Else
'Code for Yes button Press
End If


so in your case replace the comment after the no Answer with
[forms]![Company Wells Database].refresh

*** Important question *****
Is the Company Wells Database form directly linked to the Table? Is this the form that opens when you open the database? Do you see all the records or is it filtered some how? I need to know about the form so that I can help you. If the form opens with all the records and you want to filter the data to only show the newly added Well information after than you are going to have to use:

docmd.openform "Company Wells Database",,,"[Well Name-#]='" & [forms]![addWellName]![Well Name-#] & "'"

This will requery the form and display only the data for the Well name that was on the Add Wells Form. Assuming that your AddWellName form is not a continuous form and shows only one record. If it is in single form then there will be only one Well Record and it should work fine. Again, hopefully this will solve that problem.

Tyler

 

[Evenjelith]

Hey, sorry I've been away for a few days. Thanks again for your help Tyler. In the end I decided it was a lot of hassel for very little gain to have the main form open with the most recently added entry. What I have implemented with your help is a great improvement on the original database however, so I'm really happy with the end result. Thank you so much again.

The final code looked like:

If (IsNull(txtAddWellName)) Then
MsgBox ("Please enter a well name."), vbOKOnly, "Please re-enter"
Else
'Search if well name exists
Set NameCheck = CurrentDb.OpenRecordset("Select [Company Wells Database].* from [Company Wells Database] WHERE [Company Wells Database].[Well Name-#]='" & Me![txtAddWellName] & "';", 2)

'If it doesnt exist
If NameCheck.EOF Then
'Add well code
Set Varset = CurrentDb.OpenRecordset("Select [Company Wells Database].* FROM [Company Wells Database];", 2)
Varset.AddNew
Varset![Well Name-#] = Me![txtAddWellName]
Varset.Update
txtAddWellName.SetFocus

'YesNo Messagebox asking if they want to add another well
If MsgBox("'" + txtAddWellName.Text + "' added to database. Add another well?", vbYesNo, "Well Added") = vbYes Then
'Sets textbox to null for next name entry
txtAddWellName.SetFocus
txtAddWellName.Value = Null

Else
'Closes addwell form and updates company form with last well added
DoCmd.Close acForm, "Arrow Wells Database"
DoCmd.OpenForm "Arrow Wells Database"
txtAddWellName.SetFocus
txtAddWellName.Value = Null
DoCmd.Close acForm, "frmAddWellDONTDELETE"

End If

'If it does exist
Else
txtAddWellName.SetFocus
MsgBox ("'" + txtAddWellName.Text + "' already exists."), vbOKOnly, "Please re-enter"
txtAddWellName.Value = Null
End If

End If


Could I PLEASE ask you one last question. I promise it's the last one.

I'm calling a query as follows on the main form:
Set EnterKingdom = CurrentDb.OpenRecordset("KingdomDONTDELETE")

The code of the query works but I cant get it to specify one well (i.e. my WHERE statement sucks). If i tell it to run the query where [well name-#] is the same as [well name-#] from the company wells database table, the query runs and produces the desired output for each well in the database thus proving to me the relationships between tables and sql work.

When i try and specify that the [well name-#] should be the same as the [well name-#] textbox on the company wells database form:
WHERE [Company Wells Database].[Well Name-#] = [Forms]![Company Wells Database].[Well Name-#]
However the query produces a familiar error:
'3061' Too few parameters. Expected 1
The query has the company wells database table and a few others linked together. I want to retrieve unique values for a single well off a number of these tables based on the name of the well. The vb code calling this query is on the Company Wells Database form.

Can you see anything obviously wrong with the above WHERE statement. Last time there was a problem with the where it was something to do with it being a string. This is not the same as that case but I'm hoping its something as simple if im lucky.

So if you can help with this last thing that would be awesome but otherwise thanks for everything. I've really appreciated it and hopefully i can return the favour someday.

 

[TylerTand]

No Problem glad to help.

You were correct, your problem lies in your where clause. Try to remember that when you write the SQL statement in the code that it is a string. It begings docmd.runsql "". Notice how the actual sql statement is in double quotes, thus a string. If you want to put a variable into a string, in this case the well name from the form, you have to drop out of the string, get the value and then go back into the string. A string begins and ends with double quotes so you would write:

Docmd.runsql ("[Select Company Wells Database].* FROM [Company Wells Database] WHERE [Company Wells Database].[Well Name-#]='" & me![textfield on Main form that displays the Well Name searching for] & "';"

Notice that you are in the string beginning at the first double quote and ending at the [Well Name-#]=. You ended the string with the double quote before the &. But since you aren't through with the string and the value that you are dropping out of the string to get is a text, you have to put a single quote before and after the double quotes. Other wise the code reads the statement, and when it gets to the second double quote it thinks the string is over and then gives you an error basically saying, "Hey you wrote some crap after the end of the string, you made a mistake. In order to in a sense trick the code to basically wait while you retrieve the variable value you begin the variable with a single and double quote an & the variable & double quote and single quote. I was taught that the Ampersands (&) are the glue. Variables are of two types. They are either text or numbers. If it is a text field than the syntax is ' " & [text45] & " '
If the value is a number than the syntax is " & [Numberfield] & "
If the value is a date or range of dates than the syntax is #" & [DateField] & "#

You need pound signs before and after the string to tell the code that the variable is a date. if you do a date range then do:

Where [Sale Date] between #" & [Start Date] & "# AND #" & [End Date] & "#

Text need the single quotes and numbers and dates do not. I still cheat alot and use the wizard to actually write the query and then copy the SQL and then just replace the actual value with the variable. I used the me![Well Name field] syntax because the well name is on the form from which you are running the code. If you wanted to always use a value than you just declare a variable and say what it means. For instance if you wanted the Form to always open to a specific Well name you would either do this:

Docmd.runsql ("[Select Company Wells Database].* FROM [Company Wells Database] WHERE [Company Wells Database].[Well Name-#]='Big boy';"

By putting the value in single quotes it hardwires the where value into the code. You can also use a variable such as:

Dim WellName
WellName="Big Boy"
Docmd.runsql ("[Select Company Wells Database].* FROM [Company Wells Database] WHERE [Company Wells Database].[Well Name-#]='" & WellName & "';"

This time the where clause looked to your declared variable "WellName" and the value you set "Big Boy"

This really helps if you wanted to say have a drop down box that listed all of your well names and after they choose a well name it opened up a form with that well Name's information. To do this you would just use the code above and set the WellName variable to the value in the combobox. Then you can use this code for any well, instead of having to hard code the actual name in the code. There are so many possibilities once you get proficient at writing code. I am still learning every day. If you have any other questions feel free to ask them. I like helping because I was/am being helped by others that know more. Have a great day,
Tyler

 

[Evenjelith]

Hey Tyler, that was really helpful but I'm having a problem.

Firstly, if i set the name of the well specifically like you explained for debugging purposes e.g. 'Big Boy' then everything works perfectly. However, Access will not let me implement part of your code for referring to my textbox. I am using the Query design view option to enter my WHERE clause in the criteria of the column for Well Name. However the last two symbols (; and ") are causing an error so Access wont let me leave them in there. I can only assume their absence is why my query is returning no recordsets when i run it using this code.

This is the jumbled mess which is the sql version of the query (minus the ;"):

SELECT [Company Wells Database].[Well Name-#], T_Well.TotalDepth, T_Well.SurfaceLocX, T_Well.SurfaceLocY, T_Well.KBElevation, T_Well.SurfaceElevation, T_Well.Latitude, T_Well.Longitude, T_Name.Name AS Operator, T_Name_1.Name AS Lease, T_Symbol.Name AS Symbol

FROM ((((T_Well INNER JOIN T_Borehole ON T_Well.CompletionBoreholeID = T_Borehole.ID) INNER JOIN T_Symbol ON T_Borehole.SymbolID = T_Symbol.ID) INNER JOIN T_Name AS T_Name_1 ON T_Borehole.LeaseNameID = T_Name_1.ID) INNER JOIN T_Name ON T_Borehole.OperatorNameID = T_Name.ID) INNER JOIN [Company Wells Database] ON T_Borehole.Uwi = [Company Wells Database].[Well Name-#]

WHERE ((([Company Wells Database].[Well Name-#])='" & me![Well Name-#] & "') AND ((T_Name.Name)="arrow"))

ORDER BY [Company Wells Database].[Well Name-#];

If you have any idea how to fix this issue that would be awesome. Thanks for your help again. I'll keep working on it myself anyway. Bye!

 

[TylerTand]

You have a lot of inner joins. This is probably your problem. When you drag a link between two tables in the query design view you automatically create an inner join. This type of join means you only want records where the values in the two tables are the same. Here is an example of when you want a right join instead of an inner join. If I have a table that has subjects and a table that has sales. Both tables have a Subject Record ID. The Subject Record ID in the Subject Table is an autonumber field and in the Sales Table it is a number field. I can link the two tables by this field. But if I do an inner join and I have a subject that doesn't have any sales yet, I won't get any record of them from the Subject Table. See the problem. If you create a right join you will say give me all the subjects and only those Sales where the Subject Record ID is the same in both tables. Then you will get all the subjects and also all the Sales that linked to that subject. It is about the relationship between the two tables. You have created a one to one relationship. Only you can answer this but, are there records for every well in each of the tables that you have linked? If there is even one table that doesn't have a record you won't get any of those records. Here's how you change the relationship. Go back to the query design mode and right click on the link between the tables. select join properties. You will see that you have a few different choices. Option 1 is an innerjoin and it means a one to one relationship. Only use this when you know that there are records in each of the two tables. Option 2 and three are right and left joins. Option 2 will say include All records from "Subjects" and only those records from "Sales" where the joined field are equal. Now you will get this if you start in the Subject Table and drag the link to the Sales Table. Option 3 is the opposite. Its a right join if you drag from the Subject to the Sales and a left join if you drag from the Sales to the Subjects. You will have to sort it out with your tables but what ever is your main Table, I would assume at this point it is the "Company Wells Database" Table that has a record of all of your Wells. This is your main Table. It has the autonumber fields in it I assume and you link to them from your other Tables with corresponding number fields right? If so then always start at the Company Wells Table and drag to the sub tables and change the relationship to All Company wells and only "Other Table" where the join fields are the same. If you do this with all your tables you should get records again.

On a different note, did you write the SQL code:

SELECT [Company Wells Database].[Well Name-#], T_Well.TotalDepth, T_Well.SurfaceLocX, T_Well.SurfaceLocY, T_Well.KBElevation, T_Well.SurfaceElevation, T_Well.Latitude, T_Well.Longitude, T_Name.Name AS Operator, T_Name_1.Name AS Lease, T_Symbol.Name AS Symbol FROM ((((T_Well INNER JOIN T_Borehole ON T_Well.CompletionBoreholeID = T_Borehole.ID) INNER JOIN T_Symbol ON T_Borehole.SymbolID = T_Symbol.ID) INNER JOIN T_Name AS T_Name_1 ON T_Borehole.LeaseNameID = T_Name_1.ID) INNER JOIN T_Name ON T_Borehole.OperatorNameID = T_Name.ID) INNER JOIN [Company Wells Database] ON T_Borehole.Uwi = [Company Wells Database].[Well Name-#] WHERE ((([Company Wells Database].[Well Name-#])='" & me![Well Name-#] & "') AND ((T_Name.Name)="arrow")) ORDER BY [Company Wells Database].[Well Name-#];

I have never seen the joins written this way: I have many queries that have multiple tables and I built it using the query wizard and it looks totally different. Here is an example:

SELECT .......(fields from all the tables)
FROM dbo_LOCAL_APPR_LOG RIGHT JOIN (ENFORCE_V_PROCESSINGS INNER JOIN (ENFORCE_V_ARRESTS INNER JOIN((ENFORCE_V_CIVILIAN_IDENTS INNER JOIN ENFORCE_V_CIVILIANS ON ENFORCE_V_CIVILIAN_IDENTS.CID_CIV_ID = ENFORCE_V_CIVILIANS.CIV_ID) INNER JOIN ENFORCE_V_INCIDENTS ON ENFORCE_V_CIVILIANS.CIV_INC_ID = ENFORCE_V_INCIDENTS.INC_ID) ON ENFORCE_V_ARRESTS.ARS_CIV_ID = ENFORCE_V_CIVILIANS.CIV_ID) ON ENFORCE_V_PROCESSINGS.PRS_CIV_ID = ENFORCE_V_CIVILIANS.CIV_ID) ON dbo_LOCAL_APPR_LOG.SUBJECT_KEY = ENFORCE_V_CIVILIANS.CIV_ID
WHERE (((ENFORCE_V_INCIDENTS.INC_INCIDENT_DT)>(Now()-(1/2))) AND ((ENFORCE_V_INCIDENTS.INC_SITE_CD)='" & Me![Combo102] & "'));

The wizard doesn't add triple opening parenthesis after the FROM declaration? Anyway if it works no problem I was just curious.

Give that a try with the relationships. You can start simple and use two tables that you have records in one but not the other and mess with the relationships and see what results you get back.

Good luck,
Tyler

 

[Evenjelith]

Hey Tylar,

I actually got the SQL out of the SQL view of my query which i built with Wizard. I'm not sure why ours look different then. Perhaps different versions of the software. Also yes i do have a number field but its not used to link the tables. The name of the well is the only constant throughout all the tables. This is something I have to work with and cant change unfortunately.
Also, there is definetly a record of everything in every table. For example, if i hardcode in a well e.g. Big Boy, and run the query I get the results for this well. However, if I have Big Boy open on the form and run the query it finds no results. I'm pretty sure its something to do with the WHERE.

Thanks anyway.

 

[TylerTand]

O.K. post what you have an we'll see what we can do to fix the problem.
Tyler

 

[Evenjelith]

Hey, sorry it's taken me so long to reply. I havent been at work very much of late and i doubt i'll be in for the rest of the week but i wanted to let you know what was going on.
Thanks to you and a few online tutorials i managed to rewrite my query into the vb code builder and using your correct syntax for referrencing a string the method now works pretty well. THANKS HEAPS!
I'm still dealing with two bugs which i will post next time I'm in the office if your curious.

The first is kind of complex to explain but I'll try my best. I have a table called T_Name. T_Name if you want to think of it this way like a class in object oriented design. It has all the properties that a type of person should and so it has every person in there regardless of type. In a second table T_Borehole I have a number of properties. A sample are SupervisorID, DrillerID and HelperID. Each is a different kind of user.
Basically for a given borehole there is one of each of these fields. The only way to get the information for these users is to pass the respective ID to the ID field in T_Name and it will get the rest of their details. My problem is that I need to say where T_Name ID = T_Borehole SupervisorID but also that T_Name ID = T_Borehole DrillerID and that T_Name ID = HelperID. Obviously i cant make T_Name ID equal to all three. The solution would be I assume to come up with two additional instances of T_Name but i am not sure of the sql code to do this. Would you happen to know?

The second issue is that every time the well on the Company Wells Database form is change and getting data from the above query is enabled, I need to requery the tables for the new data relevent to the new well; otherwise we will be seeing the wrong thing. I have tried to use the onupdate event on the field on the form where name is stored but this will not work. Do you have a suggestion. A user can change the well name either by a search method i've included or by cycling through entries via the form controls themselves. I need to allow for both possibilities.

Anyway, thanks for your help so far. You've got almost everythign working fantastically for me so far. I wont ask for more but if you have any suggestions i'd be glad to hear them.
Thanks again!