how: VBA, filter specific records, and access information

First time member, Hello.

And sadly i am saying hello with a problem. I am a relatively new user, and i have a amateur(i think) problem.

Doing a booking system, and working in VBA
I want to check if certain records exist by date.
My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.

My query is:
strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.date)=#" & dateStr & "#));"

I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.

In pseudocode:
If(bookings.date == #xxx# AND bookings.timeslot == xx)
then 'some sort of action'

In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that

Thanks in advance


Post your answer or comment

comments powered by Disqus
Hi,

I have an employee floorplan in an excel file. The employee information are in an access file. I have an access form that can display the employee information. I would like to go to the selected employee's record in the access form when I click on an employee on the floorplan.

Can someone kindly give me a headstart on how this can be done? I can do simple VBA in excel and access, but linking excel to access form is very alien to me. Thanks in advance!

I have one form and one report. I want to print preview a specific record from form in report. how should I do? If I use command button wizard it show all records and need to scroll one by one through each record. I use Access 2003.

I need assistance on how to update a form for a specific record. What is happening is I update one record and all of the records in the form/table update for that field. They are all unbound boxes. Some are drop-down and linked to other tables. Some are check boxes. How can I update the one record without updating all of them?

My thought is to bound it but I want to bound it to the record and not to another table or list. Anyone? Anyone??

How can I get the user's OS (OS and version, including SP version) and Access version (Including Office SP) ?

Thanks,
Tal

I am sending out personalized emails based on a response to a meeting invitation. What I would like to do is open a new recordset and pull information from a specific record within that set. I have code written to perform this task automatically for each reply. Replies are entered in groups so I am trying to avoid having to requery the recordset for each reply (although if I have to...). Is there a way to use .move or another command with an adodb recordset to move to a specific record and then perform the operations on that record?

Thanks in advance.
Keith

Ok, so I am a little up in the air about where to start.

*I have a database with a list of assessments, attached to each patient. I have a form for each patient with a button on it. When the button is clicked, I want the user to be directed to an 'Assessment Status' form (that much I know how to do). The user will then select a check box if the current assessment record is ready for them to export to an excel spreadsheet. So technically by clicking this check box, I want to lock that particular record so no one else can edit it at that moment. So a user can select multiple assessment records to lock.

On the Assessment Status form, I want to have a form where the user selects whether this is a new export or a resubmission export. I then will have the user select the export path and file they want to save the export as. How do I create a listbox that will list the users previous exports?

Then I want the user to see a list of all the assessment records they locked and are ready to export. They can choose to Select All, Select One, Select more than one (but not all), and clear all from the list. So how do I do this?

And finally, I want to have an Export button and a Print Assessment List.

* I am looking for a starting point, as I am still learning, and would like to know the easiest way to do this.

Thanks in advance

Hi everyone,

I am new in using Server-Client sytem. I have SQL Server as backend and Access as frontend (Adp). Now the problem is that my code to filter data in the Form no longer works. This is my code:

Private Sub Filter_AfterUpdate()

Dim Myfilter As String
Dim Enterfilter As String

Enterfilter = Forms![Form1]![Filter]
Myfilter = "*" & Enterfilter & "*"
DoCmd.ApplyFilter , "[Family Name] like '" & Myfilter & "'"

End Sub

Note that this code works when I was still using Mdb.


For now, only this code works (button to test filter):

Private Sub Open_Click()

DoCmd.ApplyFilter , "[Family Name] LIKE 'HABERT'"

End Sub


But what I need to filter is the first few letters of Family Name not only the exact name.

Thank you for your support.

Hello all,

Is there a way to find a specific record using DAO within a recordset? (Like GotoRecord for DAO). I have a procedure to update the [quantity] of a record if its ProductID equals a specific number. Is it possible to go to that specific record and update its [quantity] without looping through the entire recordset and testing for the condition?

Thanks.

Hi,

I am trying to create a form with about 20 fields, with only 1 date field.
Each time a user fills in the form, most values (but not all) are the same as the day before, so I have it duplicate on open. I would like the date value to be automatically adjusted, keeping all other fields untouched. The date that should be filled in is always the last value from a different table.
Does anyone know how to do this?
I use Access 2010...
Many thanks!

Hello, posted this in the wrong place earlier, but...

I am doing a booking system, and working in VBA
I want to check if certain records exist by date.
My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.

My query is:
strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.datebook)=#" & dateStr & "#));"

I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.

In pseudocode:
If(bookings.date == #xxx# AND bookings.timeslot == xx)
then 'some sort of action'

In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that

Thanks in advance

Hi All,

I was wondering how to access a specific record where more than one dataset are associated with a record. e.g.

A landlord keeping a record of tenants has many tenants who exit his premises and return a couple of years later. The landlord has a client table and clientRecord table, the client table represents un changing information (name D.O.B)

the clientRecord table collects information regarding that current stay (rent collected, Date left)

if someone had say 5 seperate stays in vba how can I

a. Count the number of records (5)
b. reference the last, second from last and 1st records stored.

This process must be seamless to end users, so i can't introduce people to ID numbers etc, i need to do this programatically

thanks

Hi there,

How can I click on a button on a Form, which is bounded to a Table that has a foreign key of another table, to find the specific record (by the foreign key) in another table and change the value of one of its fields?

I do not know VB and the syntax in Access. Would you please help me out? Thanks a lot.

Is there anyway for me to (In VBA) select a specific record from a specific table?

I need to be able to pick out the Employee Grade record from a predefined Employee Name. Basically, I have a drop down list on the form from which you select the employee that you want to view the details of then clicking a "load data" button it puts their name into a text box, I need to somehow use this information in the text box as criteria for a record search. So it looks for that Employee and then takes their GradeID and lets me use it in my code (will be writing it to a variable straight away)

Is there a way to do this? If so, How?

Dave

Hi all,

Iím really in need of some help from some Access experts! Iím a newbie to Access, but I got myself busy with quite a big project... Iím trying to map out the product supply to shops in the whole country. Iíve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops donít have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, Iím desperate!!

Thank you so much,

Josephine
Access 2010 user

There are a few threads around covering this area (and I have posted on a couple) but none of them seem to be quite what I need (or I haven’t understood them ) and I have been going round in circles a bit.

I have:
• A form that is filtered by 1 of 6 VBA filters called by Select Case code on a combo box. The filters are not all on the same fields
• A report that is based on the same fields/tables as the form with no criteria set in the report query
• A button on the form that launches the report

Amongst other things, I have tried setting the report record set using

	Code:
	Set Me.Recordset = Forms!fdlgPrjDetails.Recordset

Which the VBA help says applies to reports but it doesn’t work.

I thought maybe I could convert my filters to SQL in the VBA and then send them with the docmd.openreport but I’m not entirely sure how to do this.
I got tied in knots trying to create a “where” as my filters are not the same.

I really would appreciate some pointers for the most sensible way forward. At least if I know I’m on the right path I can persevere without the worry that I am wasting my time.

Pls find attached a copy of the db for reference


================================================== ======================================

I think Ihave found the solution on another thread:
http://www.access-programmers.co.uk/...et+form+filter

It looks like the bit I wasn't getting is that the where clause can be set to me.filter to call the report only for the filtered form.

I will update the the posted db and post back when I get the chance - probably next week.
================================================== ======================================

Ok, here's what I got. I'm using an Access 2000 DB to store information on some parts we have running through the shop. Multiple people enter into the DB at various stages of the process. In the last stage, the evaluator needs to add some info and task an engineer to perfom a disposition. I've got a form set up where the evaluator can add their information to a specific record and I want Access to e-mail the selected engineer and CC the evaluator with some of the info in the record. On the form, the evaluator and engineer's names are combo boxes and there is a separate table linking the engineer's name to their e-mail and the same for the evaluators so here's my question, how can I get access to look at that specific record, copy info into the msg body, cross reference the e-mail address from the other tables and be transparent (if possible) to the evaluator when they hit submit? I've done a lot of searching on the forum and I haven't been able to find anything suitable. Thanks for the help.

Todd

p.s. I'm a mech. engineer not a CS person, so go easy on me. =)

I want to develop multiuser - Activity registry (database) programme in MS Access 2003
My Access programme is enclosed for your review.
The following functions should be done>>
Needed Help1: > > F_ RecordsView:=> should be used to view entered last record of the current user (which user information will come from F_Login )
Needed Help 2 :> F_ RecordsADD: =>should be used to ADD data. When open form, it should be point out the new record in table (T_Records) with form view (F_RecordsADD) and Current User ID

In order to organize the above functions, I have tried to use Form filter functions but it was not work well.
I shall be very much thankful, if you could provide guidance and advice to complete the above mentioned programme.
Thanks
Visva

Note: Combo box functions are work well in my system. So I only expect guidance to link forms with filter records (by User name < => Staff ID)

Hi, I originally posted this problem on the Tables forum but having read a little bit more about Access, I think that perhaps I am looking at some kind of Macro using the RecordSet functions.

"Suppose I have someone who writes an exam 10 times in one day. However, it is only the last result of that day I am interested in, not the preceding nine. When the information is entered into a spreadsheet, it will have the StudentID appearing 10 times, with the exam date with a result alongside each record. So I meant unique to the extent that each student is uniquely identified by their ID. However there could be 1000 students per day, each with differing amounts of times they have sat the exam. So, if I only want the first entry with that particular ID, can I ask Access to get rid of the next 9. Is there some way to "loop" through the data to search for each ID, then discard the rest or perhaps loop through and ask Access or Excel or whatever to take each time it first encounters a UniqueID to dump that record into another Table or spreadsheet"

Now, the problem is compounded by the fact that I also need to know the number of duplicates of each StudentID. So, an analogy is that I will need to count how many times each student has written that exam that day, as well as only ultimately being interested in the final result.

If if makes things easier, I can also structure the problem another way. What if I were to be interested in the average mark of the total number of times the student has taken the exam in that day rather than just the final result. That way, I think that it would be possible to keep all the relevant records in one table to be able to count the number of times the exam is taken, as well as then just doing an operation on it to get the average of one field per StudentID.

Any help would be greatly appreciated!

Hello Iam beginner in VBA, we have an oracle 10g database, and I must retrieve data from this server, and format these datas into Access 2003 report, and Access's charts. We will have to terminate operations by rendering .pdf file. All these operations should have done into VBA code, -not with Access 2003 wizards-.
Could you please to tell me how to do, or if it exists a tutorial, or a book relating this subject, or any informations.

Thanks for any help.

Question,

I created a database that holds information on authorizations we obtain.

What I would like to do is scan any paper works related to that specific record and create a link to the folder where I am holding the scanned document. I also would like for the person working the case to scan the documents by choosing the category, naming the file and hit a command button that opens the scanner.

I would have a form that filters by ID# and it will give me a list of all documents scan for this record (i.e. see below) and I will click the file name and the document will open.

ID # xxxx

Scan document type File Name
Insurance Card IDNoXXXXInsCard
Authorization Form IDNoXXXXAuthForm

Any ideas where I could find information on how to do this???

Thank you inadvance for your assistance.

Hello,
I have a question regarding designing a query to obtain a specified single record or row from a table. Is this even possible?
I understand top value and least value queries and filters. I've also done a lot of research in how to obtain a specified number of top or least value records.
I can't seem to find any information on how to obtain a specific record or row, after a ranking query is performed.
I have records for employees that are ranked in order of performance. Their information contains a field containing their performance score, which the ranking order is based on. It also contains a field that lists the ranking numbers in ascending order. The top performing employee is listed at the top of the order, with the ranking number one.
I need to place these employees in various positions according to their ranking position.
Whereas, the #1 ranked employee will be placed into position A, the #2 ranked employee will be placed into position B and so on.
So, I was wondering what would be the best way to do this?
Would it be best to run a macro (series of queries), which will fill the positions in sequence or to designate each position as (=) to their position in the ranking order?
1) If I did the macro, I would need to know how to design it to fill the positions in sequence.
2) If I were to designate each position to be filled by an employee in a corresponding ranking position, I would need to know how to write the expression that will place each employee according to their ranking position.
Can anyone help me?
Thanks!

Hi,

I have a form named Form1 with a subform Form2.
How can I open Form1 from another form and find a specific record in the subform?

This code only filters by customer, and not project like I want it to.

	Code:
	docmd.OpenForm "FrmProject",,,"[CustomerID]=forms![FrmMain].form![CustomerID]"


Hope that was not too confusing :P

Thanks!

I have a search form (continue) contain basic information which allow people to filter to a specific record. I have another data entry form (single form) has all the detailed information. These two forms have the same data source. the PK for the table is ParticipantID

I add a macro (I do not much VBA codes) to participantID for the search form and hopefully it can open the data entry form for that participant if I click the ParticipantID.

the macro are: open form and I use where condition :
Forms![FrmSearch]![ParticipantID] = forms![FrmDataEntry]![ParticipantID]. however, it open a new record in the data entry form?

Can somebody tell me what's wrong with it?
Or write an code for me?
Thanks.

Hi all
I have four different form - A1,A2,A3,A4. I will have one more form which is called "MasterForm". In Masterform, I will have only one text box where user will enter the information for opening the one of form( A1 or A2 or A3or A4) and searching specific record(e.g.Serial number). So, input in Masterform is like "A1-00001".
So, now I have to parse the string(A1-00001) and then open form "A1" and search record for serial number "00001".
Is it possible to do it? If yes, how can we do it? Can somebody help me on this?

Thanks
Ashvin


Not finding an answer? Try a Google search.