I have written an Access database that our department uses to store and analyze information on particular jobs. Because our company runs on an enormous SQL DB, I thought it would save some time if the basic job details (name, location, salesperson, etc) which is already stored in the SQL DB could be loaded onto one of my forms at the click of a button. The form needs this information (and more that has to be manually entered) to be complete. I wrote an SQL view and linked it into my Access DB. My idea is to be able to, when entering new jobs, type in a job number on my form, and then at the click of a button, populate several other fields from information already stored in the view. However, I'm getting run-time error 424 "object not found" - even though all relevant fields have been included on my SELECT statement in my form the appropriate INNER JOIN has been done. Here's how I'm doing it: - Private Sub LoadInfo_Click()
-
' Pulls information out of SQL view.
-
Me!SalesManager = dbo_vwBookingInfo.SM
-
Me!ProjectManager = dbo_vwBookingInfo.PM
Access does recognize the view as I've successfully written a query with it. Does anybody know how to accomplish what I'm trying to do? Not all jobs in the SQL view should go into my Access table, which is why I'm trying to have it only load information one job at a time on command...
13 7691
Instead of using visual basic, I tried writing a query to search for the information I needed, and then using a macro to have the field on the form = the field on the query.
This is the error I am now getting: ![](/images/imgserve.php?url=http://pages.sbcglobal.net/eleanor.chen/error.JPG) MMcCarthy 14,534
Recognized Expert Moderator MVP
You cannot reference the view directly. You need to use something like a recordset. As this returns all records in the view and you can only load one record at a time into the table you will need to set criteria. -
-
Private Sub LoadInfo_Click()
-
' Pulls information out of SQL view.
-
Dim db As Database
-
Dim rs As Recordset
-
Dim tmpUser As String
-
-
Set db = CurrentDb
-
' criteria can only return 1 record as only one record can be loaded into the form.
-
Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE [Fieldname]=" & yourcriteria)
-
-
Me!SalesManager = rs!SM
-
Me!ProjectManager = rs!PM
-
-
rs.Close
-
Set rs = Nothing
-
Set db = nothing
-
-
End Sub
-
-
I have written an Access database that our department uses to store and analyze information on particular jobs. Because our company runs on an enormous SQL DB, I thought it would save some time if the basic job details (name, location, salesperson, etc) which is already stored in the SQL DB could be loaded onto one of my forms at the click of a button. The form needs this information (and more that has to be manually entered) to be complete. I wrote an SQL view and linked it into my Access DB. My idea is to be able to, when entering new jobs, type in a job number on my form, and then at the click of a button, populate several other fields from information already stored in the view. However, I'm getting run-time error 424 "object not found" - even though all relevant fields have been included on my SELECT statement in my form the appropriate INNER JOIN has been done. Here's how I'm doing it: - Private Sub LoadInfo_Click()
-
' Pulls information out of SQL view.
-
Me!SalesManager = dbo_vwBookingInfo.SM
-
Me!ProjectManager = dbo_vwBookingInfo.PM
Access does recognize the view as I've successfully written a query with it. Does anybody know how to accomplish what I'm trying to do? Not all jobs in the SQL view should go into my Access table, which is why I'm trying to have it only load information one job at a time on command...
Thanks so much!
Last question - if I wanted to add a MsgBox and end the process if that particular job number does not exist in the view (if they typed it in incorrectly, for example) how would I go about doing that?
Again, very much appreciated. :)
MMcCarthy 14,534
Recognized Expert Moderator MVP
Set rs = db.OpenRecordse t("SELECT * FROM dbo_vwBookingIn fo WHERE [Fieldname]=" & yourcriteria)
If rs.RecordCount <> 0 Then
'Your code here
Else
Msgbox "This job cannot be found", vbOkOnly
End If
Thanks so much!
Last question - if I wanted to add a MsgBox and end the process if that particular job number does not exist in the view (if they typed it in incorrectly, for example) how would I go about doing that?
Again, very much appreciated. :)
Rawr...
Now I'm getting a "Too few parameters. Expected 1." run-time error on this line - Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE [BookingNumber]=" & Me!JobNumber)
-
:( I tried adding
& ";"
at the end, and to no avail. Same error.
You usually just add the SQL in between the () in an OpenRecordset, altough if it is not working, try the next:
Dim rs As RecordSet
Set rs = db.OpenRecordSe t("SELECT * FROM dbo_vwBookingIn fo WHERE [BookingNumber]=" & Me!JobNumber, dbOpenDynaset)
Note that there are various properties available for OpenRecordSet, I suggest you examine these properties when using VB/Access or SQL access, it can save you time and question-asking.
Greetings,
Miffe
I have also tried explicitly defining Forms!PreJCEntr y!JobNumber ...instead of Me!JobNumber ... still no good.
I tried this as well, and it did not work. I have searched through various websites and forums as well as the helpfiles and cannot seem to find the answer. I have double checked and rewritten the code as well... dbo_vwBookingIn fo exists, BookingNumber is the name of a field in that table, and JobNumber is a legitimate control on my form, but still the same run-time "Too few parameters" error.
You usually just add the SQL in between the () in an OpenRecordset, altough if it is not working, try the next:
Dim rs As RecordSet
Set rs = db.OpenRecordSe t("SELECT * FROM dbo_vwBookingIn fo WHERE [BookingNumber]=" & Me!JobNumber, dbOpenDynaset)
Note that there are various properties available for OpenRecordSet, I suggest you examine these properties when using VB/Access or SQL access, it can save you time and question-asking.
Greetings,
Miffe
My next test was to use
Set rs = db.OpenRecordse t("SELECT * FROM dbo_vwBookingIn fo WHERE BookingNumber=A MPASPUB00001")
Where AMPASPUB00001 was a booking number in the table... STILL same error!
I tried copying the contents of dbo_vwBookingIn fo into a new table, and using that table, and still no go as well.
Rawr, any help would be appreciated. I hope I'm not missing something obvious, but it is very frustrating.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bob |
last post by:
Currently I am using this statement to translate 3 fields in my db
thru Visual Basic. I import the data from one table to another then
call the IFF statements and the NewDate to translate the fields. Can
this be done in an Access db? I am trying to learn how to do these
things in Access vs Visual
Dim strIIF As String
Dim sstrIIF As String
Dim strNewDate As Date
|
by: James Fortune |
last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't
understand why people who know how to write code to completely replace
a front end do not write something that will automate the code that
implements managing unbound controls on forms given the superior
performance of unbound controls in a client/server environment. I can
easily understand a newbie using bound controls or someone with a
tight deadline. I guess I need...
|
by: bill.brennum |
last post by:
Hi,
This is probably a question with a simple answer, but here goes.
I have a form linked to a table. There is a field on that table that
while not showing on the form, I would like to populate via a lookup
using either a popup or subform. This is an employee name match
application that will allow a user to lookup a corporate employee and
assign them to an unmatched person.
|
by: John Phelan-Cummings |
last post by:
I'm not certain if this made the post. Sorry if it's a repeat:
Using a Button to take an autonumber from one form to populate another
autonumber field on another form.
I have a Mainform "A" with a button and code that opens another Mainform
"B"
with a subform which has the same table as Mainform "A".
The button is supposed to tell the subform with the autonumber field
|
by: agrawal.solutions |
last post by:
Hello Friends
I am asking a very silly question but i dont find any solution fo
this..
I am selectiong a recordset and want to populate a combobox where id
would be inviseble and the content would.
I am getting the recordset and the no of record but then i am unble to
populate the combobox.
I have already tried all the function starting form
for each x in ....
and while...wend and do....loop
| |
by: dmeyr |
last post by:
Hello,
I am new to Access and am having difficulty with a Dlookup function. I have a form that I wish to autopopulate 10 fields with values based on two criteria which are also fields on the form. The user would enter the two criteria in the form and the ten fields autopopulate. To achieve this, I used a Dlookup function in the Control Source of the fields I wished to autopopulate.
These Dlookups work perfectly except that my table does...
|
by: joshua.nicholes |
last post by:
I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species...
|
by: MattFitzgerald |
last post by:
My Forms & Tables:-
Main form is Frm_LE_List (contains Customer Details) Stored in Tbl_LE_List
Which contains subform Frm_VOL_References (Contains Orders known as VOL's) Stored in Tbl_VOL_References
This subform has subform Frm_Order_Lines (Contains line items for orders) Stored in Tbl_Order_Lines
I also have a table Tbl_Rate_Card_Lookup (This contains information used to populate Tbl_Order_lines
Relationships:-
Tbl_LE_List is related...
|
by: giandeo |
last post by:
Hello Experts.
Could you find a solution for this problem please!
I have the following tables in Access Database
Table Name: origin
Fields Names: country, countrycode
Table Name: make
Fields Names: countrycode, make
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |