i want to add record to a subform through another popup form but the id should remain same. I am attaching the db along with the post in which the agent and client tables are in relationship and i want to add client in the subform through a popup form. But when i fill the form and save it even after selecting the same agent. It creates a new agent id and saves the data in that id which is opposite of wha i am trying to achieve. any help on how to fix this will be highly appreciated.
Adding Data To Subform With Same ID (1 Viewer)
- Thread starter vagues0ul
- Start date
- Local time
- Today, 23:42
- Joined
- Jul 9, 2003
- Messages
- 16,271
I don't think there's much I can help you with until you get the structure sorted out. For instance looking in the passenger details table, you have "Passenger_ID" and you don't have this as an auto number? In fact you have the same number for several different passengers? So I'm not sure what you're thinking here, maybe you would like to explain.
Since making the above comment, I've done a little bit more exploration and I see that the form:- "Agent" has what at first glance looks like a subform right underneath the "ADD CLIENT" button, in fact this is just the details section of the form, and it's not a subform? I would expect an agent to have several clients, therefore I would expect the agent form the main form, to have a client form displayed on it in a Subform/subreport control a control I term a "subForm Window" - "subFormWin"... The lack of a subform reinforces my suspicion there's a fundamental problem with the design of your database.
I recommend you read up on database design, read about - normalisation, and also you might like to have a look at my blog here "Excel in access" - where I explain how a some people arrive at the situation you find yourself in.
Since making the above comment, I've done a little bit more exploration and I see that the form:- "Agent" has what at first glance looks like a subform right underneath the "ADD CLIENT" button, in fact this is just the details section of the form, and it's not a subform? I would expect an agent to have several clients, therefore I would expect the agent form the main form, to have a client form displayed on it in a Subform/subreport control a control I term a "subForm Window" - "subFormWin"... The lack of a subform reinforces my suspicion there's a fundamental problem with the design of your database.
I recommend you read up on database design, read about - normalisation, and also you might like to have a look at my blog here "Excel in access" - where I explain how a some people arrive at the situation you find yourself in.
- Local time
- Today, 23:42
- Joined
- Jul 9, 2003
- Messages
- 16,271
Ah! I see! Having a second look, or actually third look, I see it IS a subform! The image control was interfering with the display and wouldn't let me select the subform/subreport control. Let me have another look and get back to you.
Last edited:
- Local time
- Today, 23:42
- Joined
- Jul 9, 2003
- Messages
- 16,271
Right now this looks like it could be a problem, besides the problem I've identified above having duplicate ID's, which needs sorting out, if you look in the subform/subreport property sheet you will see that in the linking Fields - the parent is "agentid" and the child is "Passenger_ID" ... now I don't think that's what you intended?
- Local time
- Today, 23:42
- Joined
- Jul 9, 2003
- Messages
- 16,271
Your main table is also storing the Passengers name and passport number? This is a good recipe for problems, having the same information stored in two separate tables. However I suspect this was the initial table, and you have gone on to develop the other tables subsequently? If that's the case then it might pay to remove this redundant table.
- Local time
- Today, 23:42
- Joined
- Jul 9, 2003
- Messages
- 16,271
I don't begin to understand the relationship between the passenger and the agent, my first thoughts were it was some sort of flight booking system? But it also looks a bit like a hotel booking system, but using the wrong terms to describe the individuals. They're not passengers! They are guests!
How you proceed with the construction of your database is dependent on what information you want to store. In the case of a flight booking system, I think it is doubtful that you will want to retain information about a passenger, like who the father, mother, brother, sister was. This information would be practically irrelevant for further flights. If it were to be a flight booking system I think I would have a table just for passengers. Without any provision for recording the relationships between the passengers.
However if it was a hotel booking system, then you might want to consider recording relationships, for regular guests you might well want to establish rapport with the family, and in this case it would be good to know the relationships of the individuals...
It might sound a bit like I'm waffling here, and indeed I am a tad! However it is important to understand the business needs, the business processes before you construct the database.
This is really the point here. And indeed, I don't think I've ever seen such a good example of how the different requirements of a business would affect the construction of the database. The difference between airline passengers and hotel guests is a good Example. Very similar details are required passport, name, etc, and then the family relationship, required for one and not required for the other.
How you proceed with the construction of your database is dependent on what information you want to store. In the case of a flight booking system, I think it is doubtful that you will want to retain information about a passenger, like who the father, mother, brother, sister was. This information would be practically irrelevant for further flights. If it were to be a flight booking system I think I would have a table just for passengers. Without any provision for recording the relationships between the passengers.
However if it was a hotel booking system, then you might want to consider recording relationships, for regular guests you might well want to establish rapport with the family, and in this case it would be good to know the relationships of the individuals...
It might sound a bit like I'm waffling here, and indeed I am a tad! However it is important to understand the business needs, the business processes before you construct the database.
This is really the point here. And indeed, I don't think I've ever seen such a good example of how the different requirements of a business would affect the construction of the database. The difference between airline passengers and hotel guests is a good Example. Very similar details are required passport, name, etc, and then the family relationship, required for one and not required for the other.
Last edited:
1: The Agent table store agent related data.
2: Passenger_Details table is storing passengers data that are related to specific agent
3: One agent can have many passengers but they need to be on same agent id (eg id 57 can have many passengers detail linked to it.)
that is the main scenario i am trying to have. If i enter any passenger detail on the subform present on AGENT form (in datasheet view) then it saves the data with same agent id. But if i try to add it through another popup form then it creates a new ID for the agent (which is not what i am trying to do).
i have tried using tempvariables macros and got Agent_Name on second form but unable to get the agent_id passed to that popup form. I hav attached the ammended file again with the db and deleted the tables that are creating confusion.
2: Passenger_Details table is storing passengers data that are related to specific agent
3: One agent can have many passengers but they need to be on same agent id (eg id 57 can have many passengers detail linked to it.)
that is the main scenario i am trying to have. If i enter any passenger detail on the subform present on AGENT form (in datasheet view) then it saves the data with same agent id. But if i try to add it through another popup form then it creates a new ID for the agent (which is not what i am trying to do).
i have tried using tempvariables macros and got Agent_Name on second form but unable to get the agent_id passed to that popup form. I hav attached the ammended file again with the db and deleted the tables that are creating confusion.
yes i did notice but can you explain why cliednt it is needed there instead of agent id ?
When you add a new passenger you are adding the data to the "Passenger_Details" table, (that should be obviously). The first field is "Passenger_ID", which you use to link the Agent and the Passenger together, therefore you need to put in the value of "AgentID".
If you instead bound the combobox to "AgentID", you're creating a new record in the "Agent" table, which you've observed.
One of the minor issues is your field names and how you're naming them, I think you're confusing you self, then "Passenger_ID" isn't actually a passenger id but an agent id. A real "Passenger_ID" is a number by which you can uniquely identify a passenger like a Passport number or a Uncle Gizmo mention an autonumber field in the "Passenger_Details" table.
Attachments
-
Passenger_ID1.jpg
Similar threads
