Using visual basic to populate fields of a form from another table?

[Selesti]

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...

[Selesti]

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:

[MMcCarthy]

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...

[Selesti]

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]

Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo 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. :)

[Selesti]

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.

[miffe]

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.OpenRecordSet("SELECT * FROM dbo_vwBookingInfo 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

[Selesti]

I have also tried explicitly defining Forms!PreJCEntry!JobNumber ...instead of Me!JobNumber ... still no good.

[Selesti]

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_vwBookingInfo 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.OpenRecordSet("SELECT * FROM dbo_vwBookingInfo 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

[Selesti]

My next test was to use

Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE BookingNumber=AMPASPUB00001")

Where AMPASPUB00001 was a booking number in the table... STILL same error!

I tried copying the contents of dbo_vwBookingInfo 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.

[MMcCarthy]

If BookingNumber is text and not a number field use:

Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE [BookingNumber]='" & Me.JobNumber & "'")

My next test was to use

Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE BookingNumber=AMPASPUB00001")

Where AMPASPUB00001 was a booking number in the table... STILL same error!

I tried copying the contents of dbo_vwBookingInfo 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.

[Selesti]

If BookingNumber is text and not a number field use:

Set rs = db.OpenRecordset("SELECT * FROM dbo_vwBookingInfo WHERE [BookingNumber]='" & Me.JobNumber & "'")

You are my shining hero. I had tried extra quotation marks, but didn't realize there needed to be apostrophes. Thank you so much.

[aharding]

Hello! I want to get to the same place..I think..but am way behind you both in skill. I know I want fields to autopopulate when I select one field. I got this to happen in a query...but would like it to happen in a form. I have tried manipulating both the sample database and your codes into my database but really just don't know much about codes or Visual Basic. I think im am missing something in this whole process....help!

[MMcCarthy]

Post your question directly to the forum and a number of experts will attempt to answer it.