424,066 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

Search for Record Macro Refusing to use Variables

P: 6
I'm making a database to keep track of cases but given my lack of knowledge of VBA I'm trying to strictly stick to macros.
I'm using the SearchForRecord Command with the following parameters

Form1 : Button1 : Onclick


Object Type: Form

Object Name: Form2

Record: First

Where Condition: [Forms]![Form2]![ID] = [Forms]![Form1]![ID]

Effectively this codes goal is to find the matching Record on a second form I've experimented and by using a fixed variable this works however it doe not matter what I do. The moment I try to use two variables it defaults to the first Record.

Ive even gone as far as to make other queries with this value and they have done so successfully. even showing the desired ID in a text box so I know this variable can be used and is being read correctly but the SearchForRecord Command does not like comparing two variables.
3 Weeks Ago #1
Share this Question
Share on Google+
8 Replies

Expert Mod 2.5K+
P: 2,780
I'm making a database to keep track of cases but given my lack of knowledge of VBA I'm trying to strictly stick to macros.
Rather than trying to strictly stick to macros, I would highly encourage you to venture into the world of VBA. VBA has much more flexibility and is probably less intimidating than what you think it is, once you start to understand the basics.

What you are trying to do can be done in VBA using FindRecord.

Additionally, it would be helpful for us to see the syntax you were using when trying to use two variables. This would help us understand what might have been going awry.

Hope this hepps.
3 Weeks Ago #2

Expert Mod 15k+
P: 31,170
Is your solution as simple as :
Expand|Select|Wrap|Line Numbers
  1. ([Forms]![Form2]![ID] In([Forms]![Form1]![ID],{Some other reference}))
Or do you need to explain exactly what you're talking about here before we can give any practical help?
3 Weeks Ago #3

P: 6
In this case its a work-order database where the client ID can be selected, if ever the client info needs to be modified I created an edit list items to modify the client info. I was trying to make this new form pull the client ID from the work-order view and load the selected record accordingly for editing.

As mentioned prior I can run a query to filter the client results and only the desired name will come through, as well as simply trying to pull this value to a text box which also succeeded. its simply the SearchForRecord macro that will default to the first record.

In an ideal setting this would pull any value including values for a new record but the complexity that seemed beyond what I was willing to put into a first database.
3 Weeks Ago #4

Expert Mod 15k+
P: 31,170
I'm looking for an explanation that makes sense technically. I can't really help you with generalities. What are you working with? Forms I assume. How are they supposed to interact? What is the process you're trying to achieve?

A general explanation of what you'd like in fuzzy human terms is fine as it goes, but it leaves us with absolutely nothing to work from.

PS. I assume your answer to my question must be no. Generally it's a good idea to answer specific questions as it shows you're paying attention and investing your own time and effort into your problem.
2 Weeks Ago #5

P: 6
Sorry, I guess from my perspective it all fits together but I also have looked at everything thoroughly first hand. Let me get into the specifics.

This is the Tickets Page for case tracking (Renamed to Form 2 for simplicity). Please also note that the name displayed is only a query and the saved value is the ClientID.

I have editing in these combo-boxes disabled to avoid accidental data corruption from users.

The goal was to have it so that the onLoad macro would select the appropriate record for editing (Assuming it exists) or leave it blank for a new record. As such I created a second form to edit the client data (Renamed to Form 1 for simplicity).

This is the Editing Form (Form 1)

Notice how the queried values on the right did get successfully loaded from the previous form however the loaded record is only the first record available.

The code that makes these boxes tick :
Form 2 Name
Expand|Select|Wrap|Line Numbers
  1. SELECT Clients.[ClientName], [Clients].[ClientID] FROM Clients;
Bound Column 2
Form 1 Requested ID
Expand|Select|Wrap|Line Numbers
  1. [Forms]![Form 2]![ClientID]
Queried Name
Expand|Select|Wrap|Line Numbers
  1. SELECT Clients.ClientName, Clients.ClientID FROM Clients WHERE CLientID=[Requested ID];
OnLoad Macro Listed in first post

The main problem is that [Forms].[Form 2].[ClientID] = 3 will load the record with the ClientID=3 however :
Expand|Select|Wrap|Line Numbers
  1. [Forms].[Form 2].[ClientID] = [Forms].[Form 1].[ClientID]
  2. [Forms].[Form 2].[ClientID] = [Forms].[Form 2].[Requested ID]
will default to the first record.

The names of the items have been modified to make it easier to follow in a post and while reading. If there are mistakes they are likely only in the post but please let me know if there are any you would like me to double check.

I really do appreciate your time and patience. If there is anything I missed or didn't explain correctly/enough please let me know. I would be happy to provide further details.
2 Weeks Ago #6

Expert Mod 15k+
P: 31,170
Now I can see that you've gone to the trouble of explaining so much here it doesn't really matter so much how clear you made it (Not very I'm afraid). That you put the effort in is the big thing. Let me see if I can explain what I think you mean and you can tell me if I'm anywhere near.

You're trying to show a particular record on what you refer to as [Form 1] (Earlier as [Form1] - second picture) which is supposed to match the record selected in the ComboBox called [Name] found on [Form 2] (Earlier [Form2]) - first picture. You're using a macro with parameters set as :
Expand|Select|Wrap|Line Numbers
  1. Form1 : Button1 : Onclick
  2. SearchForRecord
  3. Object Type: Form
  4. Object Name: Form2
  5. Record: First
  6. Where Condition: [Forms]![Form2]![ID] = [Forms]![Form1]![ID]
Quite unsurprisingly, this doesn't select the record you chose but simply goes to the first record every time?

If all that is about right then your problem is the Where Condition parameter. You think of it as two forms, yet actually you are using the data from one form ([Form1].[ID]) to specify the data (IE. The recordset. Not the form at all but the data the form uses.) which is shown on [Form2]. Thus you need to specify search criteria (or [Where Condition]) that specifies the record required from the recordset. IE. The value of a Field that it's looking for. Yours specifies a Control on your form instead of a Field from the recordset.

So, guessing a little at what data you may be working with, you probably need something like :
Expand|Select|Wrap|Line Numbers
  1. Where Condition: ([ID]=[Forms]![Form2]![ID])
This assumes that your underlying recordset has a field called [ID]. You can see that it now says to search for the record where the [ID] value equals whatever value is found in [Forms]![Form2]![ID]. Also notice that the updated version makes it clearer that [Form2] is where the data is from that specifies which record you require. Your original had it back to front - except that it didn't matter as it was not done correctly anyway, but I hope you get the point.

Now, if that all works and you're happy, I would suggest going further when you feel ready to. Searching for a record is one thing. Filtering is another and more appropriate approach. It doesn't allow the user to move between records accidentally as so often happens when the required record is simply selected. If that doesn't make sense to you then don't worry too much, but if it does then that's a more reliable approach that will lead to fewer confusing problems with the use of the form.
2 Weeks Ago #7

P: 6
Okay so i played around with it a lot more using the combinations

Renamed ClientID Box to ClientName to avoid collision with Client Table

Expand|Select|Wrap|Line Numbers
  1. [Forms]![Form2]![ID]
Expand|Select|Wrap|Line Numbers
  1. [ClientName]=[Forms]![Form2]![ID]
Expand|Select|Wrap|Line Numbers
  1. [Form2]![ID]
I though my error might have been missing that its searching for the record and I had a variable that was on that record but that doesn't seem to be it. any sugestions?
1 Week Ago #8

Expert Mod 15k+
P: 31,170
My suggestion is still the same as my previous post. You try what I suggested there. It's nothing to do with renaming boxes - I assume you're referring to the Control on the form - it's about understanding that the Control is irrelevant and only the Field matters.
1 Week Ago #9

Post your reply

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